AP_TPTINV_DISTRIBUTIONS_PKG CREATE OR REPLACE PACKAGE APPS.APL_TRANSPORT_TRX_PKG AUTHID CURRENT_USER AS /* $Header: APL_TRANSPORT_TRX_PKGS.pls 1.

0 01-JAN-2011 10:00 kvbreddy ship $ */ /* From Location and To Location columns added. */ PROCEDURE Insert_Row( X_Rowid IN OUT NOCOPY VARCHAR2, X_Transaction_Id IN OUT NOCOPY NUMBER, X_Header_Id NUMBER, X_Last_Update_Date DATE, X_Last_Updated_By NUMBER, X_Creation_Date DATE, X_Created_By NUMBER, X_Last_Update_Login NUMBER DEFAULT NULL, X_Vendor_Id NUMBER, X_Vendor_Site_Id NUMBER, X_Lr_Num VARCHAR2, X_Lr_Date DATE, X_Master_Organization_Id NUMBER, X_Orgn_Code VARCHAR2 , X_Amount NUMBER, X_Gl_Account_Cc_Id NUMBER, X_Trx_Line_Num NUMBER, X_Vat_code VARCHAR2, X_Type_1099 VARCHAR2, X_Attribute_Category VARCHAR2 DEFAULT NULL, X_Attribute1 VARCHAR2 DEFAULT NULL, X_Attribute2 VARCHAR2 DEFAULT NULL, X_Attribute3 VARCHAR2 DEFAULT NULL, X_Attribute4 VARCHAR2 DEFAULT NULL, X_Attribute5 VARCHAR2 DEFAULT NULL, X_Attribute6 VARCHAR2 DEFAULT NULL, X_Attribute7 VARCHAR2 DEFAULT NULL, X_Attribute8 VARCHAR2 DEFAULT NULL, X_Attribute9 VARCHAR2 DEFAULT NULL, X_Attribute10 VARCHAR2 DEFAULT NULL, X_Org_Id NUMBER DEFAULT NULL, X_Trans_Status VARCHAR2 , X_Trans_Type VARCHAR2 , X_from_loc VARCHAR2, --KVBREDDY 05-SEP-2 008 X_to_loc VARCHAR2, --KVBREDDY 05-SEP2008 X_calling_sequence VARCHAR2, X_TPT_BILL_NUMBER VARCHAR2, X_BILL_DATE DATE);

PROCEDURE Lock_Row(

X_Rowid VARCHAR2, X_Transaction_Id NUMBER, X_Header_Id NUMBER, X_Last_Update_Date DATE, X_Last_Updated_By NUMBER, X_Creation_Date DATE, X_Created_By NUMBER, X_Last_Update_Login NUMBER DEFAULT NULL,

X_Vendor_Id NUMBER, X_Vendor_Site_Id NUMBER, X_Lr_Num VARCHAR2, X_Lr_Date DATE, X_Master_Organization_Id NUMBER, X_Orgn_Code VARCHAR2 , X_Amount NUMBER, X_Gl_Account_Cc_Id NUMBER, X_Trx_Line_Num NUMBER, X_Vat_code VARCHAR2, X_Type_1099 VARCHAR2, X_Attribute_Category VARCHAR2 DEFAULT NULL, X_Attribute1 VARCHAR2 DEFAULT NULL, X_Attribute2 VARCHAR2 DEFAULT NULL, X_Attribute3 VARCHAR2 DEFAULT NULL, X_Attribute4 VARCHAR2 DEFAULT NULL, X_Attribute5 VARCHAR2 DEFAULT NULL, X_Attribute6 VARCHAR2 DEFAULT NULL, X_Attribute7 VARCHAR2 DEFAULT NULL, X_Attribute8 VARCHAR2 DEFAULT NULL, X_Attribute9 VARCHAR2 DEFAULT NULL, X_Attribute10 VARCHAR2 DEFAULT NULL, X_Org_Id NUMBER DEFAULT NULL, X_Trans_Status VARCHAR2 , X_Trans_Type VARCHAR2 , X_from_loc VARCHAR2, X_to_loc VARCHAR2, X_calling_sequence VARCHAR2, X_TPT_BILL_NUMBER VARCHAR2, X_BILL_DATE DATE ); PROCEDURE Update_Row( X_Rowid VARCHAR2, X_Transaction_Id NUMBER, X_Header_Id NUMBER, X_Last_Update_Date DATE, X_Last_Updated_By NUMBER, X_Creation_Date DATE, X_Created_By NUMBER, X_Last_Update_Login NUMBER DEFAULT NULL, X_Vendor_Id NUMBER, X_Vendor_Site_Id NUMBER, X_Lr_Num VARCHAR2, X_Lr_Date DATE, X_Master_Organization_Id NUMBER, X_Orgn_Code VARCHAR2 , X_Amount NUMBER, X_Gl_Account_Cc_Id NUMBER, X_Trx_Line_Num NUMBER, X_Vat_code VARCHAR2, X_Type_1099 VARCHAR2, X_Attribute_Category VARCHAR2 DEFAULT NULL, X_Attribute1 VARCHAR2 DEFAULT NULL, X_Attribute2 VARCHAR2 DEFAULT NULL, X_Attribute3 VARCHAR2 DEFAULT NULL, X_Attribute4 VARCHAR2 DEFAULT NULL, X_Attribute5 VARCHAR2 DEFAULT NULL, X_Attribute6 VARCHAR2 DEFAULT NULL, X_Attribute7 VARCHAR2 DEFAULT NULL,

X_Attribute8 X_Attribute9 X_Attribute10 X_Org_Id X_Trans_Status X_Trans_Type X_from_loc 008 X_to_loc 2008

VARCHAR2 DEFAULT NULL, VARCHAR2 DEFAULT NULL, VARCHAR2 DEFAULT NULL, NUMBER DEFAULT NULL, VARCHAR2 , VARCHAR2 , VARCHAR2, --KVBREDDY 05-SEP-2 VARCHAR2, --KVBREDDY 05-SEP-

X_calling_sequence VARCHAR2, X_TPT_BILL_NUMBER VARCHAR2, X_BILL_DATE DATE ); PROCEDURE Delete_Row(X_Rowid X_calling_sequence VARCHAR2, VARCHAR2);

END APL_TRANSPORT_TRX_PKG; /

******************************************** CREATE OR REPLACE PACKAGE BODY APPS.APL_TRANSPORT_TRX_PKG AS /* $Header: APL_TRANSPORT_TRX_PKGB.pls 1.0 01-JAN-2011 10:00 */ /* From Location and To Location columns added. */ PROCEDURE Insert_Row(

kvbreddy ship $

X_Rowid IN OUT NOCOPY VARCHAR2, X_Transaction_Id IN OUT NOCOPY NUMBER, X_Header_Id NUMBER, X_Last_Update_Date DATE, X_Last_Updated_By NUMBER, X_Creation_Date DATE, X_Created_By NUMBER, X_Last_Update_Login NUMBER DEFAULT NULL, X_Vendor_Id NUMBER, X_Vendor_Site_Id NUMBER, X_Lr_Num VARCHAR2, X_Lr_Date DATE, X_Master_Organization_Id NUMBER, X_Orgn_Code VARCHAR2 ,

X_Amount NUMBER, X_Gl_Account_Cc_Id NUMBER, X_Trx_Line_Num NUMBER, X_Vat_code VARCHAR2, X_Type_1099 VARCHAR2, X_Attribute_Category VARCHAR2 DEFAULT NULL, X_Attribute1 VARCHAR2 DEFAULT NULL, X_Attribute2 VARCHAR2 DEFAULT NULL, X_Attribute3 VARCHAR2 DEFAULT NULL, X_Attribute4 VARCHAR2 DEFAULT NULL, X_Attribute5 VARCHAR2 DEFAULT NULL, X_Attribute6 VARCHAR2 DEFAULT NULL, X_Attribute7 VARCHAR2 DEFAULT NULL, X_Attribute8 VARCHAR2 DEFAULT NULL, X_Attribute9 VARCHAR2 DEFAULT NULL, X_Attribute10 VARCHAR2 DEFAULT NULL, X_Org_Id NUMBER DEFAULT NULL, X_Trans_Status VARCHAR2 , X_Trans_Type VARCHAR2 , X_From_loc VARCHAR2, --kvbreddy 05-sep -2008 X_To_loc 008 X_calling_sequence VARCHAR2, X_TPT_BILL_NUMBER VARCHAR2, X_BILL_DATE DATE IS CURSOR C IS SELECT rowid FROM APL_TRANSPORT_TRX WHERE Transaction_Id = X_Transaction_Id; CURSOR C2 IS SELECT TO_NUMBER(MAX(TRANSACTION_ID)+1) FROM APL_TRANSPO RT_TRX; current_calling_sequence VARCHAR2(2000); debug_info VARCHAR2(100); BEGIN -- Update the calling sequence current_calling_sequence := 'APL_TRANSPORT_TRX_PKG.INSERT_ROW<-'||X_calling_sequence; if (X_Transaction_Id is NULL) then debug_info := 'Open cursor C2'; OPEN C2; debug_info := 'Fetch cursor C2'; FETCH C2 INTO X_Transaction_Id; debug_info := 'Close cursor C2'; CLOSE C2; end if; debug_info := 'Insert into APL_TRANSPORT_TRX'; INSERT INTO APL_TRANSPORT_TRX( Transaction_Id, Header_Id, Last_Update_Date, Last_Updated_By, Creation_Date, Created_By, Last_Update_Login, ) VARCHAR2,--kvbreddy 05-sep-2

Vendor_Id, Vendor_Site_Id, Lr_Number, Lr_Date, Master_Organization_Id , Orgn_Code, Amount , Gl_Account_Cc_Id, Trx_Line_Num, Vat_Code, Type_1099, Attribute_Category, Attribute1, Attribute2, Attribute3, Attribute4, Attribute5, Attribute6, Attribute7, Attribute8, Attribute9, Attribute10, Org_Id, Tran_status, Trans_Type, From_Location, --added by kvbreddy on 05-sep-2008 To_Location, TPT_BILL_NUMBER , BILL_DATE ) VALUES (X_Transaction_Id, X_Header_Id , X_Last_Update_Date , X_Last_Updated_By , X_Creation_Date , X_Created_By , X_Last_Update_Login , X_Vendor_Id , X_Vendor_Site_Id , X_Lr_Num , X_Lr_Date , X_Master_Organization_Id , X_Orgn_Code, X_Amount, X_Gl_Account_Cc_Id , X_Trx_Line_Num, X_Vat_code, X_Type_1099, X_Attribute_Category, X_Attribute1 , X_Attribute2 , X_Attribute3 , X_Attribute4 , X_Attribute5 , X_Attribute6 , X_Attribute7 , X_Attribute8 , X_Attribute9 , X_Attribute10 , X_Org_Id , X_Trans_Status ,

X_Trans_Type, X_From_loc, X_To_loc, X_TPT_BILL_NUMBER , X_BILL_DATE ); debug_info := 'Open cursor C'; OPEN C; debug_info := 'Fetch cursor C'; FETCH C INTO X_Rowid; if (C%NOTFOUND) then debug_info := 'Close cursor C - ROW NOTFOUND'; CLOSE C; Raise NO_DATA_FOUND; end if; debug_info := 'Close cursor C'; CLOSE C; EXCEPTION WHEN OTHERS THEN if (SQLCODE <> -20001) then FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG'); FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM); FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence); FND_MESSAGE.SET_TOKEN('PARAMETERS', 'Rowid = '||X_Rowid ||', Transac tion_id = '||TO_CHAR(X_Transaction_Id) ); FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info); end if; APP_EXCEPTION.RAISE_EXCEPTION; END Insert_Row;

PROCEDURE Lock_Row( X_Rowid

VARCHAR2, X_Transaction_Id X_Header_Id NUMBER, X_Last_Update_Date DATE, X_Last_Updated_By X_Creation_Date X_Created_By X_Last_Update_Login X_Vendor_Id NUMBER, X_Vendor_Site_Id X_Lr_Num VARCHAR2, X_Lr_Date X_Master_Organization_Id NUMBER, X_Orgn_Code VARCHAR2 , X_Amount NUMBER, X_Gl_Account_Cc_Id NUMBER, X_Trx_Line_Num NUMBER, X_Vat_code VARCHAR2, X_Type_1099 VARCHAR2, X_Attribute_Category X_Attribute1 X_Attribute2 X_Attribute3 X_Attribute4

NUMBER, NUMBER, DATE, NUMBER, NUMBER DEFAULT NULL, NUMBER, DATE,

VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2

DEFAULT DEFAULT DEFAULT DEFAULT DEFAULT

NULL, NULL, NULL, NULL, NULL,

X_Attribute5 VARCHAR2 DEFAULT NULL, X_Attribute6 VARCHAR2 DEFAULT NULL, X_Attribute7 VARCHAR2 DEFAULT NULL, X_Attribute8 VARCHAR2 DEFAULT NULL, X_Attribute9 VARCHAR2 DEFAULT NULL, X_Attribute10 VARCHAR2 DEFAULT NULL, X_Org_Id NUMBER DEFAULT NULL, X_Trans_Status VARCHAR2 , X_Trans_Type VARCHAR2 , X_from_loc VARCHAR2, --KVBREDDY 05-SEP-2008 X_to_loc VARCHAR2, --KVBREDDY 05-SEP-2008 X_calling_sequence VARCHAR2, X_TPT_BILL_NUMBER VARCHAR2, X_BILL_DATE DATE ) IS CURSOR C IS SELECT * FROM APL_TRANSPORT_TRX WHERE rowid = X_Rowid FOR UPDATE of Transaction_Id NOWAIT; Recinfo C%ROWTYPE; current_calling_sequence VARCHAR2(2000); debug_info VARCHAR2(100); BEGIN -- Update the calling sequence current_calling_sequence := 'APL_TRANSPORT_TRX_PKG.LOCK_ROW<-'||X_calling_sequence; debug_info := 'Open cursor C'; OPEN C; debug_info := 'Fetch cursor C'; FETCH C INTO Recinfo; if (C%NOTFOUND) then debug_info := 'Close cursor C - ROW NOTFOUND'; CLOSE C; FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED'); APP_EXCEPTION.Raise_Exception; end if; debug_info := 'Close cursor C'; CLOSE C; if ( (Recinfo.Header_id = X_Header_Id) AND (Recinfo.Transaction_id = X_Transaction_Id) AND (Recinfo.vendor_id = X_Vendor_Id) AND (Recinfo.vendor_site_id = X_Vendor_Site_Id) AND (Recinfo.lr_number = X_Lr_Num) AND (Recinfo.lr_date = X_Lr_Date) AND (Recinfo.Master_Organization_Id = X_Master_Organization_Id) AND (Recinfo.Orgn_Code = X_Orgn_Code) AND (Recinfo.Amount = X_Amount) AND (Recinfo.Gl_Account_Cc_Id = X_Gl_Account_Cc_Id) AND (Recinfo.Trx_Line_Num = X_Trx_Line_Num) AND (Recinfo.Trans_Type = X_Trans_Type) AND (Recinfo.org_id = X_Org_Id) ) then null; else FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');

APP_EXCEPTION.Raise_Exception; end if; if(((Recinfo.attribute_category = X_Attribute_Category) OR ( (Recinfo.a ttribute_category IS NULL) AND (X_Attribute_Category IS NULL))) AND ( (Recinfo.attribute1 = X_Attribute1) OR ( (Recinfo.attrib ute1 IS NULL) AND (X_Attribute1 IS NULL))) AND ( (Recinfo.attribute2 = X_Attribute2) OR ( (Recinfo.attrib ute2 IS NULL) AND (X_Attribute2 IS NULL))) AND ( (Recinfo.attribute3 = X_Attribute3) OR ( (Recinfo.attrib ute3 IS NULL) AND (X_Attribute3 IS NULL))) AND ( (Recinfo.attribute4 = X_Attribute4) OR ( (Recinfo.attri bute4 IS NULL) AND (X_Attribute4 IS NULL))) AND ( (Recinfo.attribute5 = X_Attribute5) OR ( (Recinfo.attrib ute5 IS NULL) AND (X_Attribute5 IS NULL))) AND ( (Recinfo.attribute6 = X_Attribute6) OR ( (Recinfo.attribute 6 IS NULL) AND (X_Attribute6 IS NULL))) AND ( (Recinfo.attribute7 = X_Attribute7) OR ( (Recinfo.attri bute7 IS NULL) AND (X_Attribute7 IS NULL))) AND ( (Recinfo.attribute8 = X_Attribute8) OR ( (Recinfo.attri bute8 IS NULL) AND (X_Attribute8 IS NULL))) AND ( (Recinfo.attribute9 = X_Attribute9) OR ( (Recinfo.attri bute9 IS NULL) AND (X_Attribute9 IS NULL))) AND ( (Recinfo.attribute10 = X_Attribute10) OR ((Recinfo.attribute 10 IS NULL) AND (X_Attribute10 IS NULL))) )then return; else FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED'); APP_EXCEPTION.Raise_Exception; end if; EXCEPTION WHEN OTHERS THEN if (SQLCODE <> -20001) then IF (SQLCODE = -54) THEN FND_MESSAGE.SET_NAME('SQLAP','AP_RESOURCE_BUSY'); ELSE FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG'); FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM); FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence); FND_MESSAGE.SET_TOKEN('PARAMETERS', 'Rowid = '||X_Rowid ||', Trans action_Id = '||TO_CHAR(X_Transaction_Id) ); FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info); END IF; end if; APP_EXCEPTION.RAISE_EXCEPTION; END Lock_Row; PROCEDURE Update_Row( X_Rowid

VARCHAR2, X_Transaction_Id

NUMBER,

X_Header_Id X_Last_Update_Date

NUMBER, DATE, X_Last_Updated_By NUMBER, X_Creation_Date DATE, X_Created_By NUMBER, X_Last_Update_Login NUMBER DEFAULT NULL, X_Vendor_Id NUMBER, X_Vendor_Site_Id NUMBER, X_Lr_Num VARCHAR2, X_Lr_Date DATE, X_Master_Organization_Id NUMBER, X_Orgn_Code VARCHAR2 , X_Amount NUMBER, X_Gl_Account_Cc_Id NUMBER, X_Trx_Line_Num NUMBER, X_Vat_code VARCHAR2, X_Type_1099 VARCHAR2, X_Attribute_Category VARCHAR2 DEFAULT NULL, X_Attribute1 VARCHAR2 DEFAULT NULL, X_Attribute2 VARCHAR2 DEFAULT NULL, X_Attribute3 VARCHAR2 DEFAULT NULL, X_Attribute4 VARCHAR2 DEFAULT NULL, X_Attribute5 VARCHAR2 DEFAULT NULL, X_Attribute6 VARCHAR2 DEFAULT NULL, X_Attribute7 VARCHAR2 DEFAULT NULL, X_Attribute8 VARCHAR2 DEFAULT NULL, X_Attribute9 VARCHAR2 DEFAULT NULL, X_Attribute10 VARCHAR2 DEFAULT NULL, X_Org_Id NUMBER DEFAULT NULL, X_Trans_Status VARCHAR2 , X_Trans_Type VARCHAR2 , X_from_loc VARCHAR2, --KVBREDDY 05-SEP-2008 X_to_loc VARCHAR2, --KVBREDDY 05-SEP-2008 X_calling_sequence VARCHAR2, X_TPT_BILL_NUMBER VARCHAR2, X_BILL_DATE DATE ) IS current_calling_sequence VARCHAR2(2000); debug_info VARCHAR2(100); BEGIN -- Update the calling sequence current_calling_sequence := 'APL_TRANSPORT_TRX_PKG.UPDATE_ROW<-'||X_calling_sequence; debug_info := 'Update APL_TRANSPORT_TRX'; UPDATE APL_TRANSPORT_TRX SET Transaction_id = X_Transaction_id, Header_id = X_Header_Id , Last_Update_Date = X_Last_Update_Date , Last_Updated_By = X_Last_Updated_By , Creation_Date = X_Creation_Date , Created_By = X_Created_By , Last_Update_Login = X_Last_Update_Login, Vendor_Id = X_Vendor_Id , Vendor_Site_Id = X_Vendor_Site_Id, Lr_Number = X_Lr_Num ,

Lr_Date = X_Lr_Date , Master_Organization_Id = X_Master_Organization_Id , Orgn_Code = X_Orgn_Code , Amount = X_Amount , Gl_Account_Cc_Id = X_Gl_Account_Cc_Id , Trx_Line_Num = X_Trx_Line_Num, Vat_code = X_Vat_code, Type_1099 = X_Type_1099, Attribute_Category = X_Attribute_Category , Attribute1 = X_Attribute1 , Attribute2 = X_Attribute2 , Attribute3 = X_Attribute3 , Attribute4 = X_Attribute4 , Attribute5 = X_Attribute5 , Attribute6 = X_Attribute6 , Attribute7 = X_Attribute7 , Attribute8 = X_Attribute8 , Attribute9 = X_Attribute9 , Attribute10 = X_Attribute10 , Org_Id = X_Org_Id , Tran_Status = X_Trans_Status, Trans_type = X_Trans_Type, from_location = X_from_loc, -- KVBREDDY ON 05-SEP-2008 to_location = X_to_loc, -- KVBREDDY ON 05-SEP-2008 TPT_BILL_NUMBER = X_TPT_BILL_NUMBER , BILL_DATE = X_BILL_DATE WHERE rowid = X_Rowid; if (SQL%NOTFOUND) then Raise NO_DATA_FOUND; end if; EXCEPTION WHEN OTHERS THEN if (SQLCODE <> -20001) then FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG'); FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM); FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence); FND_MESSAGE.SET_TOKEN('PARAMETERS', 'Rowid = '||X_Rowid ||', Transacti on_Id = '||TO_CHAR(X_Transaction_Id) ); FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info); end if; APP_EXCEPTION.RAISE_EXCEPTION; END Update_Row; PROCEDURE Delete_Row(X_Rowid VARCHAR2, IS current_calling_sequence VARCHAR2(2000); debug_info VARCHAR2(100); BEGIN -- Update the calling sequence current_calling_sequence := 'APL_TRANSPORT_TRX_PKG.DELETE_ROW<-'||X_calling_sequence; X_calling_sequence VARCHAR2)

debug_info := 'Delete from APL_TRANSPORT_TRX'; DELETE FROM APL_TRANSPORT_TRX WHERE rowid = X_Rowid; if (SQL%NOTFOUND) then Raise NO_DATA_FOUND; end if; EXCEPTION WHEN OTHERS THEN if (SQLCODE <> -20001) then FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG'); FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM); FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence); FND_MESSAGE.SET_TOKEN('PARAMETERS', 'Rowid = '||X_Rowid ); FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info); end if; APP_EXCEPTION.RAISE_EXCEPTION; END Delete_Row; END APL_TRANSPORT_TRX_PKG; /

******************************************************************************** *********************** CREATE OR REPLACE PACKAGE BODY APPS.AP_TPTINV_DISTRIBUTIONS_PKG as /*$Header: AP_TPTINV_DISTRIBUTIONS_PKGB.pls 1.0 01-JAN-2010 11.00 KVBREDDY shi p $*/ PROCEDURE insert_from_apl_tr_trx ( X_invoice_id X_Header_ID X_GL_Date X_Period_Name X_Type_1099 X_Income_Tax_Region X_Offset_VAT_Code X_Freight_Ccid X_calling_sequence IN IN IN IN IN IN IN IN IN number, number, date, varchar2, varchar2, varchar2, varchar2, varchar2, varchar2)

IS -- CURSOR dist_set_ccids_cur IS CURSOR trx_ccids_cur IS SELECT Gl_Account_Cc_Id FROM APL_Transport_Trx WHERE Header_Id = X_Header_id ORDER BY TRX_LINE_NUM; CURSOR select from where and and emp_cur(l_vendor_id in NUMBER) is employee_id po_vendors_ap_v active_flag = 'Y' enabled_flag = 'Y' vendor_id = l_vendor_id;

CURSOR pa_related_cur(l_line_num in NUMBER, l_tax_code_id_tab in NUMBER, l_recovery_rate_tab in NUMBER, l_amt_incl_flag in VARCHAR2) is SELECT ai.invoice_id, ai.vendor_id, att.trx_line_num, 'ITEM', att.gl_account_cc_id, att.amount, ai.exchange_rate, ai.exchange_rate_type, ai.exchange_date, nvl(att.lr_number, ai.description)||'-'||att.tpt_bill_number||'-' ||att.bill_date, nvl(att.type_1099, X_Type_1099), l_tax_code_id_tab, 'N', l_recovery_rate_tab, 'N', 'N', 'N', 'N', 'N', ai.batch_id, ai.created_by, sysdate, ai.last_update_login, sysdate, att.last_updated_by, sp.set_of_books_id, 'U', decode(gl.account_type,'A','Y','N'), 'E', ai.ussgl_transaction_code, decode( nvl(att.type_1099,x_type_1099), '','', x_income_tax_ region), null project_accounting_contex, null project_id, null task_id, null award_id, null expenditure_type, null expenditure_organization_id, null , ai.awt_group_id, decode(ai.auto_tax_calc_flag, 'L', l_amt_incl_flag, 'T', l_amt_in cl_flag, ''), decode(ai.auto_tax_calc_flag, 'L', 'N', 'T', 'N', ''),

att.attribute1,att.attribute2, att.attribute3, att.attribute4, att.attribute5, att.attribute6, att.attribute7, att.attribute8, att.attribute9, att.attribute10, att.attribute_category FROM apl_transport_trx att, gl_code_combinations gl, ap_system_parameters SP, ap_invoices AI WHERE att.header_id = X_header_id AND att.trx_line_num = l_line_num AND att.gl_account_cc_id = gl.code_combination_id AND AI.invoice_id = X_invoice_id order by att.trx_line_num;

cursor segment1(p_header_id number) is select ---gcc.segment1, ath.last_updated_by,ath.last_update_login, sum(att.amount) line_amount from apl_transport_headers_all ath, apl_transport_trx_all att, gl_code_combinations_kfv gcc where ath.header_id = p_header_id and ath.header_id = att.header_id and gcc.code_combination_id = att.gl_account_cc_id group by --gcc.segment1, ath.last_updated_by,ath.last_update_login ; l_chart_of_accounts_id NUMBER(15); l_ccid NUMBER(15); l_amount_to_distribute number := 0; l_base_amount_to_distribute number := 0; l_dist_set_percent number := 0; l_distribution_total number := 0; l_distribution_base_total number := 0; l_next_dist number := 0; l_auto_tax_calc_flag VARCHAR2(1); l_amount_includes_tax_flag VARCHAR2(1); l_vendor_id number; l_vendor_site_id number; l_inv_vat_code AP_INVOICES.VAT_CODE%TYPE; l_inv_vat_code_id AP_TAX_CODES.TAX_ID%TYPE; l_vat_code AP_TAX_CODES.NAME%TYPE; l_invoice_date DATE; l_exchange_rate number; l_invoice_currency_code VARCHAR2(15); l_tax_id AP_TAX_CODES.TAX_ID%TYPE; l_recovery_rate number; l_enable_non_recoverable_tax FINANCIALS_SYSTEM_PARAMETERS.NON_RECOVERABLE _TAX_FLAG%TYPE; l_tax_type AP_TAX_CODES.TAX_TYPE%TYPE; l_tax_description AP_TAX_CODES.DESCRIPTION%TYPE; l_gl_allow_tax_override GL_TAX_OPTION_ACCOUNTS.ALLOW_TAX_CODE_OVERRI DE_FLAG%TYPE; l_status BOOLEAN; l_invoice_distribution_id AP_INVOICE_DISTRIBUTIONS.INVOICE_DISTRIBUTIO N_ID%TYPE;

l_invoice_id AP_INVOICES.INVOICE_ID%TYPE; l_base_currency_code AP_SYSTEM_PARAMETERS.BASE_CURRENCY_CODE%TYPE; l_tr_trx_line_number APL_TRANSPORT_TRX.TRX_LINE_NUM%TYPE; l_line_type_code AP_INVOICE_DISTRIBUTIONS.LINE_TYPE_LOOKUP_CODE%TYPE; l_dist_code_combination_id AP_INVOICE_DISTRIBUTIONS.DIST_CODE_COMBINATION_ID %TYPE; l_amount number :=0 ; l_base_amount number := 0; l_exchange_rate_type AP_INVOICES.EXCHANGE_RATE_TYPE%TYPE; l_exchange_date AP_INVOICES.EXCHANGE_DATE%TYPE; l_description AP_INVOICES.DESCRIPTION%TYPE; l_posted_flag AP_INVOICE_DISTRIBUTIONS.POSTED_FLAG%TYPE; l_accrual_posted_flag AP_INVOICE_DISTRIBUTIONS.ACCRUAL_POSTED_FLAG%TYPE; l_cash_posted_flag AP_INVOICE_DISTRIBUTIONS.CASH_POSTED_FLAG%TYPE; l_batch_id AP_INVOICE_DISTRIBUTIONS.BATCH_ID%TYPE; l_created_by AP_INVOICE_DISTRIBUTIONS.CREATED_BY%TYPE; l_last_update_login APL_TRANSPORT_TRX.LAST_UPDATE_LOGIN%TYPE; l_sysdate DATE; l_last_update_by AP_INVOICE_DISTRIBUTIONS.LAST_UPDATED_BY%TYPE; l_set_of_books_id AP_SYSTEM_PARAMETERS.SET_OF_BOOKS_ID%TYPE; l_assets_addition_flag AP_INVOICE_DISTRIBUTIONS.ASSETS_ADDITION_FLAG%TYPE; l_assets_tracking_flag AP_INVOICE_DISTRIBUTIONS.ASSETS_TRACKING_FLAG%TYPE; l_pa_addition_flag AP_INVOICE_DISTRIBUTIONS.PA_ADDITION_FLAG%TYPE; l_ussgl_transaction_code AP_INVOICE_DISTRIBUTIONS.USSGL_TRANSACTION_CODE%TYP E ; l_income_tal_region AP_INVOICE_DISTRIBUTIONS.INCOME_TAX_REGION%TYPE; l_project_accounting_context AP_INVOICE_DISTRIBUTIONS.PROJECT_ACCOUNTING_CON TEXT%TYPE; l_project_id AP_INVOICE_DISTRIBUTIONS.PROJECT_ID%TYPE; l_task_id AP_INVOICE_DISTRIBUTIONS.TASK_ID%TYPE; l_award_id AP_INVOICE_DISTRIBUTIONS.AWARD_ID%TYPE; l_expenditure_type AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_TYPE%TYPE; l_expenditure_organization_id AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ORGANIZAT ION_ID%TYPE; l_expenditure_item_date AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ITEM_DATE%TYPE; l_awt_group_id AP_INVOICE_DISTRIBUTIONS.AWT_GROUP_ID%TYPE; l_tax_calculated_flag AP_INVOICES.AUTO_TAX_CALC_FLAG%TYPE; l_attribute1 APL_TRANSPORT_TRX.ATTRIBUTE1%TYPE; l_attribute2 APL_TRANSPORT_TRX.ATTRIBUTE2%TYPE; l_attribute3 APL_TRANSPORT_TRX.ATTRIBUTE3%TYPE; l_attribute4 APL_TRANSPORT_TRX.ATTRIBUTE4%TYPE; l_attribute5 APL_TRANSPORT_TRX.ATTRIBUTE5%TYPE; l_attribute6 APL_TRANSPORT_TRX.ATTRIBUTE6%TYPE; l_attribute7 APL_TRANSPORT_TRX.ATTRIBUTE7%TYPE; l_attribute8 APL_TRANSPORT_TRX.ATTRIBUTE8%TYPE; l_attribute9 APL_TRANSPORT_TRX.ATTRIBUTE9%TYPE; l_attribute10 APL_TRANSPORT_TRX.ATTRIBUTE10%TYPE; l_attribute_category APL_TRANSPORT_TRX.ATTRIBUTE_CATEGORY%TYPE; l_employee_id PO_VENDORS_AP_V.EMPLOYEE_ID%TYPE; l_tax_code_id number(15); l_tax_code_override_flag varchar2(1); l_tax_recovery_rate number; l_tax_recovery_override_flag varchar2(1); l_tax_recoverable_flag varchar2(1); user_id number; l_Type_1099 varchar2(10) := ''; l_billable_flag varchar2(60) := ''; l_msg_application varchar2(25); l_msg_type varchar2(25);

l_msg_token1 l_msg_token2 l_msg_token3 l_msg_count l_msg_data l_invoice_line_no

varchar2(30); varchar2(30); varchar2(30); number; varchar2(30); number := 0;

TYPE VatCodeDSetTyp IS TABLE OF APL_TRANSPORT_TRX.VAT_CODE%TYPE INDEX BY BINARY_INTEGER; TYPE RecoveryRateTyp IS TABLE OF AP_INVOICE_DISTRIBUTIONS.TAX_RECOVERY_RATE% TYPE INDEX BY BINARY_INTEGER; TYPE DAmtInvDisType IS TABLE OF AP_INVOICE_DISTRIBUTIONS.AMOUNT%TYPE INDEX BY BINARY_INTEGER; TYPE TaxIDTyp IS TABLE OF AP_INVOICE_DISTRIBUTIONS.TAX_CODE_ID%TYPE INDEX BY BINARY_INTEGER; TYPE DistCCIDTyp IS TABLE OF INDEX BY BINARY_INTEGER; APL_TRANSPORT_TRX .GL_ACCOUNT_CC_ID%TYPE

TYPE TrTrxLNumTyp IS TABLE OF APL_TRANSPORT_TRX.TRX_LINE_NUM%TYPE INDEX BY BINARY_INTEGER; vatcode_tab taxid_tab recoveryrate_tab dccid_tab trtrxlinenum_tab i t current_calling_sequence debug_info l_key_value_list l_dbi_key_value_list VatCodeDSetTyp; TaxIDTyp; RecoveryRateTyp; DistCCIDTyp; TrTrxLNumTyp; BINARY_INTEGER := 0; BINARY_INTEGER := 0; VARCHAR2(2000); VARCHAR2(100); gl_ca_utility_pkg.r_key_value_arr; ap_dbi_pkg.r_dbi_key_value_arr;

l_invoice_type_lookup_code ap_invoices.invoice_type_lookup_code%TYPE; l_prepay_dist_code_ccid ap_invoice_distributions.dist_code_combination_id %TYPE; l_sys_link_function l_org_id BEGIN MO_GLOBAL.SET_POLICY_CONTEXT('S',l_org_id); -- Update the calling sequence current_calling_sequence := 'AP_INVOICE_DISTRIBUTIONS_PKG.insert_from_apl_transport_trx<-'|| X_calling_sequence; user_id := to_number(FND_PROFILE.VALUE('USER_ID')); -- In order to insert from a distribution set, we have to -- have a distribution_set_id. If we don't have one then VARCHAR2(2); number := fnd_profile.value('ORG_ID');

-- exit the procedure if (X_Header_ID is null) then return; end if; debug_info := 'Validate ccid in the apl_transport_trx'; -- get chart_of_accounts_id from ap_system_parameters SELECT gsob.chart_of_accounts_id into l_chart_of_accounts_id FROM ap_system_parameters ap, gl_sets_of_books gsob WHERE ap.set_of_books_id = gsob.set_of_books_id; OPEN trx_ccids_cur; LOOP FETCH trx_ccids_cur INTO l_ccid; EXIT WHEN trx_ccids_cur%NOTFOUND; If( NOT (AP_UTILITIES_PKG.IS_CCID_VALID(l_ccid, l_chart_of_accounts_id, X_Gl_date, current_calling_sequence))) then fnd_message.set_name('SQLAP', 'AP_INVALID_CCID'); app_exception.raise_exception; End If; END LOOP; debug_info := 'Select from AP_DISTRIBUTION_SETS'; -- Determine the amount to distribute, vendor information , -- tax information from AP_INVOICES. SELECT decode(AI.tax_amount, '',AI.invoice_amount, decode(X_offset_vat _code,'',AI.invoice_amount - AI.tax_amount, AI.invoice_amount)), AI.vendor_id, AI.vendor_site_id, AI.vat_code, AI.invoice_date, AI.exchange_rate, AI.invoice_currency_code INTO l_amount_to_distribute, l_vendor_id, l_vendor_site_id, l_inv_vat_code, l_invoice_date, l_exchange_rate, l_invoice_currency_code FROM ap_invoices AI WHERE invoice_id = X_invoice_id; /* Execute the select statement only if l_inv_vat_code is not null */ If l_inv_vat_code is not null then SELECT tax_id INTO l_inv_vat_code_id FROM ap_tax_codes WHERE name = l_inv_vat_code AND nvl(enabled_flag,'N') = 'Y' AND trunc(l_invoice_date) between nvl(start_date,trun c(l_invoice_date))

AND

nvl(inactive_date,trunc(l_invoice_date)); End If;

l_base_amount_to_distribute := AP_UTILITIES_PKG.Ap_Round_Currency( NVL(l_amount_to_distribute,0) * l_exchange_rate , l_invoice_c urrency_code); -- Read whether we allow recoverability. SELECT nvl(non_recoverable_tax_flag, 'N') INTO l_enable_non_recoverable_tax FROM financials_system_parameters;

-- Redetermine the amount to distribute if automatically creating -- create distribution if (X_freight_ccid is not null) then debug_info := 'SELECT from AP_INVOICES'; SELECT INTO FROM WHERE end if; l_amount_to_distribute - AI.freight_amount l_amount_to_distribute ap_invoices AI invoice_id = X_invoice_id;

-- Determine the auto tax flag, and whether amount includes/excludes ta x. debug_info := 'Get Calculation Level and Amount includes tax flag'; SELECT AI.auto_tax_calc_flag, PVS.amount_includes_tax_flag, nvl(PVS.prepay_code_combination_id, SP.prepay_code_combination_id), AI.invoice_type_lookup_code INTO l_auto_tax_calc_flag, l_amount_includes_tax_flag, l_prepay_dist_code_ccid, l_invoice_type_lookup_code FROM ap_invoices AI, po_vendor_sites PVS, ap_system_parameters SP WHERE AI.invoice_id = X_invoice_id AND PVS.vendor_site_id = AI.vendor_site_id; If (l_invoice_type_lookup_code ='EXPENSE REPORT') Then l_sys_link_function :='ER' ; Else l_sys_link_function :='VI' ; End if; -select sum(amount) into l_amount from apl_transport_trx where he

ader_id=X_header_id; -- Read the tax code and percent distribution from distribution set -- lines for each dist set line into PL/SQL tables. debug_info := 'Get the Tax name and percent distribution for each dist s et line'; for units in segment1(X_Header_ID) loop l_invoice_line_no := nvl(l_invoice_line_no,0)+1; insert into ap_invoice_lines_all (invoice_id, line_number, line_type_lookup_code, last_update_date, last_updated_by, creation_date, created_by, last_update_login, accounting_date, period_name, amount, base_amount, rounding_amt, description, type_1099, income_tax_region, set_of_books_id, assets_tracking_flag, asset_book_type_code, asset_category_id, line_source, generate_dists, match_type, prorate_across_all_items, deferred_acctg_flag, wfapproval_status, discarded_flag, cancelled_flag, final_match_flag, requester_id, org_id) values ( X_invoice_id, -- invoice_id l_invoice_line_no, -- line_number 'ITEM', -- line_type_lookup_code sysdate, -- last_update_date units.last_updated_by, -- last_updated_by sysdate, -- creation_date units.last_updated_by, -- created_by units.last_update_login, -- last_update_login x_gl_date, -- accounting_date x_period_name, -- period_name units.line_amount, -- amount l_base_amount, -- base_amount null, -- rounding_amt 'Transportation Invoice', -- description l_type_1099, -- type_1099 null, -- income_tax_region 2023, --l_set_of_books_id, -- set_of_books_id 'N', -- assets_tracking_flag

null, null, 'AUTO INVOICE CREATION', 'D', 'NOT_MATCHED', 'N', 'N', 'NOT REQUIRED', 'N', 'N', 'N', null, l_org_id);

-------------

asset_book_type_code asset_category_id line_source generate_dists match_type prorate_across_all_items deferred_acctg_flag wfapproval_status discarded_flag cancelled_flag final_match_flag REQUESTER_ID

FOR trtrxrec IN x_line_num ) --KVBREDDY ON 26-SEP-2011

(SELECT null vat_code , amount, gl_account_cc_id,tr FROM APL_TRANSPORT_TRX WHERE header_id = X_header_id ORDER BY trx_line_num

--and gl_account_cc_id in (select code_combination _id from gl_code_combinations where segment1=units.segment1)) LOOP i := i + 1; vatcode_tab(i) := trtrxrec.vat_code; If (l_invoice_type_lookup_code <> 'PREPAYMENT' or l_project_id is null) then dccid_tab(i) := trtrxrec.gl_account_cc_id; Else dccid_tab(i) := l_prepay_dist_code_ccid; End if; trtrxlinenum_tab(i) := trtrxrec.trx_line_num; debug_info := 'Getting the value of the employee id before looping'; OPEN emp_cur(l_vendor_id); LOOP FETCH emp_cur into l_employee_id; EXIT WHEN emp_cur%NOTFOUND; END LOOP; CLOSE emp_cur; -- Loop through all dist set lines and gather whether we should -- use the tax code from the dist set -- or another for the new distributions. Also read recovery rates. -- FOR i IN 1..t LOOP. Commented for bug2371965 debug_info := 'Getting the value of the default tax code '; -- Bug 2672129 Nullifying values before assigning for each distribution l_vat_code := null; l_tax_id := null; /*AP_TAX_DEFAULT_PKG.get_default_tax_code(

P_LINE_LOCATION_ID =>'', P_VENDOR_ID =>l_vendor_id, P_VENDOR_SITE_ID =>l_vendor_site_id, P_CODE_COMBINATION_ID =>dccid_tab(i), P_CONCATENATED_SEGMENTS =>'', P_INV_TAX_NAME =>l_inv_vat_code, P_TEMPLATE_TAX_NAME =>vatcode_tab(i), P_SHIP_TO_LOCATION_ID =>'', P_SHIP_TO_LOC_ORG_ID =>'', P_INVENTORY_ITEM_ID =>'', P_ITEM_ORG_ID =>'', P_TAX_CODE_DATE =>l_invoice_date, P_TAX_CODE =>l_vat_code, P_TAX_ID =>l_tax_id, P_TAX_TYPE =>l_tax_type, P_DESCRIPTION =>l_tax_description, P_ALLOW_TAX_CODE_OVERRIDE_FLAG =>l_gl_allow_tax_override, APPL_SHORT_NAME =>'SQLAP', FUNC_SHORT_NAME =>'AP_INSERT_FROM_TPTTRX', P_CALLING_SEQUENCE =>current_calling_sequence);*/ IF (l_vat_code <> l_inv_vat_code THEN vatcode_tab(i) := l_inv_vat_code; taxid_tab(i) := l_inv_vat_code_id; ELSE vatcode_tab(i) := l_vat_code; taxid_tab(i) := l_tax_id; END IF; -- If we enabled recoverability and the tax code is not -- null, then default the recovery rate, otherwise, -- assume no recovery rate. if (nvl(l_enable_non_recoverable_tax, 'N') = 'N' OR (nvl(l_enable_n on_recoverable_tax, 'N') = 'Y' and l_tax_id is null)) then recoveryrate_tab(i) := ''; else debug_info := 'Getting the value of the default tax rate'; /* AP_TAX_RECOVERY_PKG.GET_DEFAULT_RATE( P_TAX_CODE => vatcode_tab(i), P_TAX_ID => taxid_tab(i), P_TAX_DATE => l_invoice_date, P_CODE_COMBINATION_ID => dccid_tab(i), P_VENDOR_ID => l_vendor_id, P_DISTRIBUTION_ID => '', P_TAX_USER_OVERRIDE_FLAG => 'N', P_USER_TAX_RECOVERY_RATE => '', P_CONCATENATED_SEGMENTS => '', P_VENDOR_SITE_ID => l_vendor_site_id, P_INVENTORY_ITEM_ID AND l_auto_tax_calc_flag = 'Y')

=> '', P_ITEM_ORG_ID => '', APPL_SHORT_NAME => 'SQLAP', FUNC_SHORT_NAME => 'AP_INSERT_FROM_DSET', P_CALLING_SEQUENCE => current_calling_sequence, P_TAX_RECOVERY_RATE => l_recovery_rate);*/ recoveryrate_tab(i) := l_recovery_rate; end if; debug_info := 'Fetching the data for project validation '; OPEN pa_related_cur(trtrxlinenum_tab(i), taxid_tab(i), recoveryra te_tab(i), l_amount_includes_tax_flag); LOOP FETCH pa_related_cur INTO l_invoice_id, l_vendor_id, l_tr_trx_line_number, l_line_type_code, l_dist_code_combination_id, l_amount, l_exchange_rate, l_exchange_rate_type, l_exchange_date, l_description, l_Type_1099, l_tax_code_id, l_tax_code_override_flag, l_tax_recovery_rate, l_tax_recovery_override_flag, l_tax_recoverable_flag, l_posted_flag, l_accrual_posted_flag, l_cash_posted_flag, l_batch_id, l_created_by, l_sysdate, l_last_update_login, l_sysdate, l_last_update_by, l_set_of_books_id, l_assets_addition_flag, l_assets_tracking_flag, l_pa_addition_flag, l_ussgl_transaction_code, l_income_tal_region, l_project_accounting_context, l_project_id, l_task_id, l_award_id, l_expenditure_type, l_expenditure_organization_id, l_expenditure_item_date, l_awt_group_id, l_amount_includes_tax_flag,

l_tax_calculated_flag, l_attribute1, l_attribute2, l_attribute3, l_attribute4, l_attribute5, l_attribute6, l_attribute7, l_attribute8, l_attribute9, l_attribute10, l_attribute_category; EXIT WHEN pa_related_cur%NOTFOUND; END LOOP; CLOSE pa_related_cur; l_amount := AP_UTILITIES_PKG.Ap_Round_Currency( NVL(l_amount,0), l_i nvoice_currency_code); l_base_amount := AP_UTILITIES_PKG.Ap_Round_Currency( NVL(l_amount,0) * l_exchange_rate, l_base_currency_code); if ( l_project_id is not null) Then debug_info := 'Validating the project details by calling PA API';

PA_TRANSACTIONS_PUB.VALIDATE_TRANSACTION( X_PROJECT_ID => l_project_id, X_TASK_ID => l_task_id, X_EI_DATE => l_expenditure_item_date, X_EXPENDITURE_TYPE => l_expenditure_type, X_NON_LABOR_RESOURCE => null, X_PERSON_ID => l_employee_id, X_QUANTITY => '1', X_denom_currency_code=> l_invoice_currency_code, X_acct_currency_code => l_base_currency_code, X_denom_raw_cost => l_amount, X_acct_raw_cost => l_base_amount, X_acct_rate_type => l_exchange_rate_type, X_acct_rate_date => l_exchange_date, X_acct_exchange_rate => l_exchange_rate, X_TRANSFER_EI => null, X_INCURRED_BY_ORG_ID => l_expenditure_organization_id, X_NL_RESOURCE_ORG_ID => null, X_TRANSACTION_SOURCE => l_sys_link_function, X_CALLING_MODULE => 'apiindib.pls', X_VENDOR_ID => l_vendor_id, X_ENTERED_BY_USER_ID => user_id, X_ATTRIBUTE_CATEGORY => l_attribute_category, X_ATTRIBUTE1 => l_attribute1, X_ATTRIBUTE2 => l_attribute2, X_ATTRIBUTE3 => l_attribute3, X_ATTRIBUTE4 => l_attribute4, X_ATTRIBUTE5 => l_attribute5, X_ATTRIBUTE6 => l_attribute6, X_ATTRIBUTE7 => l_attribute7,

X_ATTRIBUTE8 X_ATTRIBUTE9 X_ATTRIBUTE10 X_msg_application X_msg_type X_msg_token1 X_msg_token2 X_msg_token3 X_msg_count X_msg_data X_BILLABLE_FLAG

=> => => => => => => => => => =>

l_attribute8, l_attribute9, l_attribute10, l_msg_application, l_msg_type, l_msg_token1, l_msg_token2, l_msg_token3, l_msg_count, l_msg_data, l_billable_flag);

if (l_msg_data is not null) then FND_MESSAGE.SET_NAME(l_msg_application, l_msg_data); if (l_msg_application <> 'PA') Then fnd_message.set_token('PATC_MSG_TOKEN1',l_msg_token1); fnd_message.set_token('PATC_MSG_TOKEN2',l_msg_token2); fnd_message.set_token('PATC_MSG_TOKEN3',l_msg_token3); end if; app_exception.raise_exception; end if; End if; IF l_award_id IS NOT NULL THEN debug_info := 'Validating the award details by calling GMS API'; GMS_AP_API2.VALIDATE_TRANSACTION (X_project_id => l_project_id, X_task_id => l_task_id, X_award_id => l_award_id, X_expenditure_type => l_expenditure_type, X_expenditure_item_date => l_expenditure_item_date, X_calling_sequence => 'apiindib.pls', X_msg_application => l_msg_application, X_msg_type => l_msg_type, X_msg_count => l_msg_count, X_msg_data => l_msg_data) ; IF l_msg_data IS NOT NULL THEN FND_MESSAGE.SET_NAME(l_msg_application, l_msg_data); APP_EXCEPTION.RAISE_EXCEPTION; END IF; END IF; debug_info := 'Inserting into ap_invoice_distributions after validatio n ';

/* insert into ap_invoice_lines_all ( invoice_id, line_number, line_type_lookup_code, last_update_date, last_updated_by, creation_date, created_by,

last_update_login, accounting_date, period_name, amount, base_amount, rounding_amt, description, type_1099, income_tax_region, set_of_books_id, assets_tracking_flag, asset_book_type_code, asset_category_id, line_source, generate_dists, match_type, prorate_across_all_items, deferred_acctg_flag, wfapproval_status, discarded_flag, cancelled_flag, final_match_flag, requester_id, org_id) values ( l_invoice_id, -- invoice_id l_tr_trx_line_number, -- line_number 'ITEM', -- line_type_lookup_code sysdate, -- last_update_date l_created_by, -- last_updated_by sysdate, -- creation_date l_last_update_by, -- created_by l_last_update_login, -- last_update_login x_gl_date, -- accounting_date x_period_name, -- period_name l_amount, -- amount l_base_amount, -- base_amount null, -- rounding_amt 'Transportation Invoice', -- description l_type_1099, -- type_1099 null, -- income_tax_region l_set_of_books_id, -- set_of_books_id 'N', -- assets_tracking_flag null, -- asset_book_type_code null, -- asset_category_id 'AUTO INVOICE CREATION', -- line_source 'D', -- generate_dists 'NOT_MATCHED', -- match_type 'N', -- prorate_across_all_items 'N', -- deferred_acctg_flag 'NOT REQUIRED', -- wfapproval_status 'N', -- discarded_flag 'N', -- cancelled_flag 'N', -- final_match_flag null, -- REQUESTER_ID l_org_id); */ INSERT INTO ap_invoice_distributions_all( invoice_id, distribution_line_number,

invoice_distribution_id, line_type_lookup_code, dist_code_combination_id, accounting_date, period_name, amount, base_amount, exchange_rate, exchange_rate_type, exchange_date, description, type_1099, tax_code_id, tax_code_override_flag, tax_recovery_rate, tax_recovery_override_flag, tax_recoverable_flag, posted_flag, accrual_posted_flag, cash_posted_flag, batch_id, created_by, creation_date, last_update_login, last_update_date, last_updated_by, set_of_books_id, assets_addition_flag, assets_tracking_flag, pa_addition_flag, ussgl_transaction_code, income_tax_region, project_accounting_context, project_id, task_id, award_id, expenditure_type, expenditure_organization_id, expenditure_item_date, awt_group_id, amount_includes_tax_flag, tax_calculated_flag, attribute1, attribute2, attribute3, attribute4, attribute5, attribute6, attribute7, attribute8, attribute9, attribute10, attribute_category, org_id, invoice_line_number, reversal_flag, matched_uom_lookup_code, distribution_class) values( l_invoice_id, i, --l_tr_trx_line_number,

ap_invoice_distributions_s.nextval, l_line_type_code, DECODE(l_invoice_type_lookup_code,'PREPAYMENT', decode(l_project_id, null, l_dist_code_combination_id, l_prepay_dist_code_ccid), l_dist_code_combination_id), X_Gl_Date, X_Period_Name, l_amount, l_base_amount, l_exchange_rate, l_exchange_rate_type, l_exchange_date, l_description, l_Type_1099, l_tax_code_id, l_tax_code_override_flag, l_tax_recovery_rate, l_tax_recovery_override_flag, l_tax_recoverable_flag, l_posted_flag, l_accrual_posted_flag, l_cash_posted_flag, l_batch_id, l_created_by, l_sysdate, l_last_update_login, l_sysdate, l_last_update_by, l_set_of_books_id, l_assets_addition_flag, l_assets_tracking_flag, l_pa_addition_flag, l_ussgl_transaction_code, l_income_tal_region, l_project_accounting_context, l_project_id, l_task_id, l_award_id, l_expenditure_type, l_expenditure_organization_id, l_expenditure_item_date, l_awt_group_id, l_amount_includes_tax_flag, l_tax_calculated_flag, l_attribute1, l_attribute2, l_attribute3, l_attribute4, l_attribute5, l_attribute6, l_attribute7, l_attribute8, l_attribute9, l_attribute10, l_attribute_category, l_org_id, l_invoice_line_no, --l_tr_trx_line_number, 'N', 'N',

'PERMANENT'); end loop; END LOOP;

debug_info := 'Calling MRC api: 1'; SELECT invoice_distribution_id BULK COLLECT INTO l_key_value_list FROM ap_invoice_distributions WHERE invoice_id = X_invoice_id; /* AP_MRC_ENGINE_PKG.Maintain_MRC_Data ( p_operation_mode => 'INSERT', p_table_name => 'AP_INVOICE_DISTRIBUTIONS_ALL', p_key_value => NULL, p_key_value_list => l_key_value_list, p_calling_sequence => current_calling_sequence); */ SELECT invoice_distribution_id BULK COLLECT INTO l_dbi_key_value_list FROM ap_invoice_distributions WHERE invoice_id = X_invoice_id; AP_DBI_PKG.Maintain_DBI_Summary (p_table_name => 'AP_INVOICE_DISTRIBUTIONS', p_operation => 'I', p_key_value1 => X_invoice_id, p_key_value_list => l_dbi_key_value_list, p_calling_sequence => current_calling_sequence); EXCEPTION WHEN OTHERS THEN IF (SQLCODE <> -20001) THEN FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG'); FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM); FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence); FND_MESSAGE.SET_TOKEN('PARAMETERS', 'X_header_ID = '||X_header_ID ||', X_GL_Date = ' ||X_GL_Date ||', X_Period_Name = ' ||X_Period_Name ||', X_Type_1099 = ' ||X_Type_1099 ||', X_Income_Tax_Region = ' ||X_Income_Tax_Region ||', X_Offset_VAT_Code = ' ||X_Offset_VAT_Code ||', X_invoice_id = ' ||X_invoice_id ); FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info); END IF; APP_EXCEPTION.RAISE_EXCEPTION; END insert_from_apl_tr_trx; END AP_TPTINV_DISTRIBUTIONS_PKG ;