CREATE OR REPLACE PACKAGE APPS.UPDATE_PKG
AS
PROCEDURE main (
errbuf OUT VARCHAR2,
retcode OUT VARCHAR2
);
PROCEDURE UPDATE ;
END UPDATE_PKG;
/
============ **************** =============
CREATE OR REPLACE PACKAGE BODY APPS.UPDATE_PKG
AS
PROCEDURE main (
errbuf OUT VARCHAR2,
retcode OUT VARCHAR2
) IS
begin
--Calling Procedure to update the PERIOD_NAME and ACCOUNTING_DATE in Invoice Distribution Lines
UPDATE ;
--End
end main;
PROCEDURE UPDATE is
V_PERIOD_NAME VARCHAR2(20);
V_ACCOUNTING_DATE DATE;
V_COUNT number;
CURSOR CUR IS
SELECT
AIDA.ROWID ,
AIDA.INVOICE_ID ,
AIDA.DIST_CODE_COMBINATION_ID ,
AIDA.LAST_UPDATE_DATE ,
AIDA.LAST_UPDATED_BY ,
AIDA.ACCOUNTING_DATE ,
AIDA.PERIOD_NAME ,
AIDA.SET_OF_BOOKS_ID ,
AIDA.AMOUNT ,
AIDA.DESCRIPTION ,
AIDA.TYPE_1099 ,
AIDA.TAX_CODE_ID ,
AIDA.POSTED_FLAG ,
AIDA.BATCH_ID,
AIDA.QUANTITY_INVOICED ,
AIDA.UNIT_PRICE ,
AIDA.MATCH_STATUS_FLAG ,
AIDA.ATTRIBUTE_CATEGORY ,
AIDA.ATTRIBUTE1 ,
AIDA.ATTRIBUTE2 ,
AIDA.ATTRIBUTE3 ,
AIDA.ATTRIBUTE4 ,
AIDA.ATTRIBUTE5 ,
AIDA.PREPAY_AMOUNT_REMAINING ,
AIDA.ASSETS_ADDITION_FLAG ,
AIDA.ASSETS_TRACKING_FLAG ,
AIDA.DISTRIBUTION_LINE_NUMBER ,
AIDA.LINE_TYPE_LOOKUP_CODE ,
AIDA.PO_DISTRIBUTION_ID ,
AIDA.BASE_AMOUNT ,
AIDA.EXCHANGE_RATE ,
AIDA.EXCHANGE_RATE_TYPE ,
AIDA.EXCHANGE_DATE ,
PA_ADDITION_FLAG ,
AIDA.JE_BATCH_ID ,
AIDA.POSTED_AMOUNT ,
AIDA.POSTED_BASE_AMOUNT ,
AIDA.ENCUMBERED_FLAG ,
AIDA.ACCRUAL_POSTED_FLAG ,
AIDA.CASH_POSTED_FLAG ,
AIDA.LAST_UPDATE_LOGIN ,
CASH_JE_BATCH_ID ,
AIDA.STAT_AMOUNT ,
AIDA.ATTRIBUTE11 ,
AIDA.ATTRIBUTE12 ,
AIDA.ATTRIBUTE13 ,
AIDA.ATTRIBUTE14 ,
AIDA.ATTRIBUTE6 ,
AIDA.ATTRIBUTE7 ,
AIDA.ATTRIBUTE8 ,
AIDA.ATTRIBUTE9 ,
AIDA.ATTRIBUTE10 ,
AIDA.ATTRIBUTE15 ,
AIDA.ACCTS_PAY_CODE_COMBINATION_ID ,
RATE_VAR_CODE_COMBINATION_ID ,
PRICE_VAR_CODE_COMBINATION_ID ,
EXCHANGE_RATE_VARIANCE ,
INVOICE_PRICE_VARIANCE ,
BASE_INVOICE_PRICE_VARIANCE ,
REVERSAL_FLAG ,
AIDA.PARENT_INVOICE_ID ,
AIDA.INCOME_TAX_REGION ,
AIDA.FINAL_MATCH_FLAG ,
AIDA.USSGL_TRANSACTION_CODE ,
AIDA.USSGL_TRX_CODE_CONTEXT ,
AIDA.EXPENDITURE_ITEM_DATE ,
AIDA.EXPENDITURE_ORGANIZATION_ID ,
AIDA.EXPENDITURE_TYPE ,
AIDA.PA_QUANTITY ,
AIDA.PROJECT_ID ,
AIDA.TASK_ID ,
AIDA.PROJECT_ACCOUNTING_CONTEXT ,
QUANTITY_VARIANCE ,
BASE_QUANTITY_VARIANCE ,
PACKET_ID ,
AIDA.AWT_FLAG,
AIDA.AWT_GROUP_ID ,
AWT_TAX_RATE_ID ,
AWT_GROSS_AMOUNT ,
AIDA.REFERENCE_1 ,
AIDA.REFERENCE_2 ,
AIDA.ORG_ID ,
OTHER_INVOICE_ID ,
AWT_INVOICE_ID ,
AWT_ORIGIN_GROUP_ID ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
PROGRAM_UPDATE_DATE ,
REQUEST_ID ,
AMOUNT_INCLUDES_TAX_FLAG ,
TAX_CODE_OVERRIDE_FLAG ,
TAX_RECOVERY_RATE ,
TAX_RECOVERY_OVERRIDE_FLAG ,
TAX_RECOVERABLE_FLAG ,
AIDA.AWARD_ID,
START_EXPENSE_DATE ,
MERCHANT_DOCUMENT_NUMBER ,
MERCHANT_NAME ,
MERCHANT_TAX_REG_NUMBER ,
MERCHANT_TAXPAYER_ID ,
COUNTRY_OF_SUPPLY ,
MERCHANT_REFERENCE ,
ACCOUNTING_EVENT_ID
FROM AP_INVOICES_ALL AIA,
AP_INVOICE_DISTRIBUTIONS_ALL AIDA
WHERE AIA.INVOICE_NUM in ('332766')
AND AIDA.POSTED_FLAG = 'N'
AND DISTRIBUTION_LINE_NUMBER in (5)
AND AIA.INVOICE_ID = AIDA.INVOICE_ID FOR UPDATE;
V_USER_ID NUMBER;
V_RESP_ID NUMBER;
V_RESP_APPL_ID NUMBER;
BEGIN
FND_PROFILE.GET('USER_ID',V_USER_ID);
FND_PROFILE.GET('RESP_ID',V_RESP_ID);
FND_PROFILE.GET('RESP_APPL_ID',V_RESP_APPL_ID);
FND_GLOBAL.APPS_INITIALIZE(V_USER_ID,V_RESP_ID,V_RESP_APPL_ID);
FOR REC IN CUR LOOP
BEGIN
V_ACCOUNTING_DATE := '01-AUG-13';
AP_AID_TABLE_HANDLER_PKG.UPDATE_ROW
(P_ROWID => REC.ROWID ,
P_INVOICE_ID => REC.INVOICE_ID ,
P_DIST_CODE_COMBINATION_ID => REC.DIST_CODE_COMBINATION_ID ,
P_LAST_UPDATE_DATE => REC.LAST_UPDATE_DATE ,
P_LAST_UPDATED_BY => REC.LAST_UPDATED_BY ,
P_ACCOUNTING_DATE => V_ACCOUNTING_DATE,--REC.ACCOUNTING_DATE ,
P_PERIOD_NAME => REC.PERIOD_NAME,
P_SET_OF_BOOKS_ID => REC.SET_OF_BOOKS_ID ,
P_AMOUNT => REC.AMOUNT ,
P_DESCRIPTION => REC.DESCRIPTION ,
P_TYPE_1099 => REC.TYPE_1099 ,
P_TAX_CODE_ID => REC.TAX_CODE_ID,
P_POSTED_FLAG => REC.POSTED_FLAG ,
P_BATCH_ID => REC.BATCH_ID,
P_QUANTITY_INVOICED => REC.QUANTITY_INVOICED,
P_UNIT_PRICE => REC.UNIT_PRICE ,
P_MATCH_STATUS_FLAG => REC.MATCH_STATUS_FLAG ,
P_ATTRIBUTE_CATEGORY => REC.ATTRIBUTE_CATEGORY ,
P_ATTRIBUTE1 => REC.ATTRIBUTE1 ,
P_ATTRIBUTE2 => REC.ATTRIBUTE2 ,
P_ATTRIBUTE3 => REC.ATTRIBUTE3 ,
P_ATTRIBUTE4 => REC.ATTRIBUTE4 ,
P_ATTRIBUTE5 => REC.ATTRIBUTE5 ,
P_PREPAY_AMOUNT_REMAINING => REC.PREPAY_AMOUNT_REMAINING ,
P_ASSETS_ADDITION_FLAG => REC.ASSETS_ADDITION_FLAG ,
P_ASSETS_TRACKING_FLAG => REC.ASSETS_TRACKING_FLAG ,
P_DISTRIBUTION_LINE_NUMBER => REC.DISTRIBUTION_LINE_NUMBER ,
P_LINE_TYPE_LOOKUP_CODE => REC.LINE_TYPE_LOOKUP_CODE ,
P_PO_DISTRIBUTION_ID => REC.PO_DISTRIBUTION_ID ,
P_BASE_AMOUNT => REC.BASE_AMOUNT ,
P_EXCHANGE_RATE => REC.EXCHANGE_RATE ,
P_EXCHANGE_RATE_TYPE => REC.EXCHANGE_RATE_TYPE ,
P_EXCHANGE_DATE => REC.EXCHANGE_DATE ,
P_PA_ADDITION_FLAG => REC.PA_ADDITION_FLAG ,
P_JE_BATCH_ID => REC.JE_BATCH_ID ,
P_POSTED_AMOUNT => REC.POSTED_AMOUNT ,
P_POSTED_BASE_AMOUNT => REC.POSTED_BASE_AMOUNT ,
P_ENCUMBERED_FLAG => REC.ENCUMBERED_FLAG ,
P_ACCRUAL_POSTED_FLAG => REC.ACCRUAL_POSTED_FLAG ,
P_CASH_POSTED_FLAG => REC.CASH_POSTED_FLAG ,
P_LAST_UPDATE_LOGIN => REC.LAST_UPDATE_LOGIN ,
P_CASH_JE_BATCH_ID => REC.CASH_JE_BATCH_ID ,
P_STAT_AMOUNT => REC.STAT_AMOUNT ,
P_ATTRIBUTE11 => REC.ATTRIBUTE11,
P_ATTRIBUTE12 => REC.ATTRIBUTE12 ,
P_ATTRIBUTE13 => REC.ATTRIBUTE13 ,
P_ATTRIBUTE14 => REC.ATTRIBUTE14 ,
P_ATTRIBUTE6 => REC.ATTRIBUTE6 ,
P_ATTRIBUTE7 => REC.ATTRIBUTE7 ,
P_ATTRIBUTE8 => REC.ATTRIBUTE8 ,
P_ATTRIBUTE9 => REC.ATTRIBUTE9 ,
P_ATTRIBUTE10 => REC.ATTRIBUTE10 ,
P_ATTRIBUTE15 => REC.ATTRIBUTE15 ,
P_ACCTS_PAY_CODE_COMB_ID => REC.ACCTS_PAY_CODE_COMBINATION_ID,
P_RATE_VAR_CODE_COMBINATION_ID => REC.RATE_VAR_CODE_COMBINATION_ID ,
P_PRICE_VAR_CODE_COMB_ID => REC.PRICE_VAR_CODE_COMBINATION_ID ,
P_EXCHANGE_RATE_VARIANCE => REC.EXCHANGE_RATE_VARIANCE ,
P_INVOICE_PRICE_VARIANCE => REC.INVOICE_PRICE_VARIANCE ,
P_BASE_INVOICE_PRICE_VARIANCE => REC.BASE_INVOICE_PRICE_VARIANCE ,
P_REVERSAL_FLAG => REC.REVERSAL_FLAG ,
P_PARENT_INVOICE_ID => REC.PARENT_INVOICE_ID ,
P_INCOME_TAX_REGION => REC.INCOME_TAX_REGION ,
P_FINAL_MATCH_FLAG => REC.FINAL_MATCH_FLAG ,
P_USSGL_TRANSACTION_CODE => REC.USSGL_TRANSACTION_CODE,
P_USSGL_TRX_CODE_CONTEXT => REC.USSGL_TRX_CODE_CONTEXT ,
P_EXPENDITURE_ITEM_DATE => REC.EXPENDITURE_ITEM_DATE ,
P_EXPENDITURE_ORGANIZATION_ID => REC.EXPENDITURE_ORGANIZATION_ID ,
P_EXPENDITURE_TYPE => REC.EXPENDITURE_TYPE ,
P_PA_QUANTITY => REC.PA_QUANTITY ,
P_PROJECT_ID => REC.PROJECT_ID ,
P_TASK_ID => REC.TASK_ID ,
P_PROJECT_ACCOUNTING_CONTEXT => REC.PROJECT_ACCOUNTING_CONTEXT ,
P_QUANTITY_VARIANCE => REC.QUANTITY_VARIANCE ,
P_BASE_QUANTITY_VARIANCE => REC.BASE_QUANTITY_VARIANCE,
P_PACKET_ID => REC.PACKET_ID ,
P_AWT_FLAG => REC.AWT_FLAG ,
P_AWT_GROUP_ID => REC.AWT_GROUP_ID ,
P_AWT_TAX_RATE_ID => REC.AWT_TAX_RATE_ID ,
P_AWT_GROSS_AMOUNT => REC.AWT_GROSS_AMOUNT ,
P_REFERENCE_1 => REC.REFERENCE_1 ,
P_REFERENCE_2 => REC.REFERENCE_2 ,
P_ORG_ID => REC.ORG_ID ,
P_OTHER_INVOICE_ID => REC.OTHER_INVOICE_ID,
P_AWT_INVOICE_ID => REC.AWT_INVOICE_ID ,
P_AWT_ORIGIN_GROUP_ID => REC.AWT_ORIGIN_GROUP_ID ,
P_PROGRAM_APPLICATION_ID => REC.PROGRAM_APPLICATION_ID,
P_PROGRAM_ID => REC.PROGRAM_ID ,
P_PROGRAM_UPDATE_DATE => REC.PROGRAM_UPDATE_DATE ,
P_REQUEST_ID => REC.REQUEST_ID ,
P_AMOUNT_INCLUDES_TAX_FLAG => REC.AMOUNT_INCLUDES_TAX_FLAG ,
P_TAX_CODE_OVERRIDE_FLAG => REC.TAX_CODE_OVERRIDE_FLAG ,
P_TAX_RECOVERY_RATE => REC.TAX_RECOVERY_RATE ,
P_TAX_RECOVERY_OVERRIDE_FLAG => REC.TAX_RECOVERY_OVERRIDE_FLAG ,
P_TAX_RECOVERABLE_FLAG => REC.TAX_RECOVERABLE_FLAG,
P_AWARD_ID => REC.AWARD_ID ,
P_START_EXPENSE_DATE => REC.START_EXPENSE_DATE ,
P_MERCHANT_DOCUMENT_NUMBER => REC.MERCHANT_DOCUMENT_NUMBER,
P_MERCHANT_NAME => REC.MERCHANT_NAME ,
P_MERCHANT_TAX_REG_NUMBER => REC.MERCHANT_TAX_REG_NUMBER ,
P_MERCHANT_TAXPAYER_ID => REC.MERCHANT_TAXPAYER_ID ,
P_COUNTRY_OF_SUPPLY => REC.COUNTRY_OF_SUPPLY ,
P_MERCHANT_REFERENCE => REC.MERCHANT_REFERENCE ,
P_CALLING_SEQUENCE => 2);
UPDATE ap_accounting_events_all
SET accounting_date = '01-AUG-2013'
WHERE accounting_event_id = REC.ACCOUNTING_EVENT_ID
AND event_status_code IN ('CREATED')
AND org_id = REC.ORG_ID
AND source_id = REC.INVOICE_ID;
v_count := cur%rowcount ;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error Occured while updating the AP Invoice DIstrbution :'||SQLCODE||'-'||SQLERRM);
END;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Total '||v_count||' Line rows got updated');
COMMIT;
end UPDATE;
end UPDATE_PKG;
/
AS
PROCEDURE main (
errbuf OUT VARCHAR2,
retcode OUT VARCHAR2
);
PROCEDURE UPDATE ;
END UPDATE_PKG;
/
============ **************** =============
CREATE OR REPLACE PACKAGE BODY APPS.UPDATE_PKG
AS
PROCEDURE main (
errbuf OUT VARCHAR2,
retcode OUT VARCHAR2
) IS
begin
--Calling Procedure to update the PERIOD_NAME and ACCOUNTING_DATE in Invoice Distribution Lines
UPDATE ;
--End
end main;
PROCEDURE UPDATE is
V_PERIOD_NAME VARCHAR2(20);
V_ACCOUNTING_DATE DATE;
V_COUNT number;
CURSOR CUR IS
SELECT
AIDA.ROWID ,
AIDA.INVOICE_ID ,
AIDA.DIST_CODE_COMBINATION_ID ,
AIDA.LAST_UPDATE_DATE ,
AIDA.LAST_UPDATED_BY ,
AIDA.ACCOUNTING_DATE ,
AIDA.PERIOD_NAME ,
AIDA.SET_OF_BOOKS_ID ,
AIDA.AMOUNT ,
AIDA.DESCRIPTION ,
AIDA.TYPE_1099 ,
AIDA.TAX_CODE_ID ,
AIDA.POSTED_FLAG ,
AIDA.BATCH_ID,
AIDA.QUANTITY_INVOICED ,
AIDA.UNIT_PRICE ,
AIDA.MATCH_STATUS_FLAG ,
AIDA.ATTRIBUTE_CATEGORY ,
AIDA.ATTRIBUTE1 ,
AIDA.ATTRIBUTE2 ,
AIDA.ATTRIBUTE3 ,
AIDA.ATTRIBUTE4 ,
AIDA.ATTRIBUTE5 ,
AIDA.PREPAY_AMOUNT_REMAINING ,
AIDA.ASSETS_ADDITION_FLAG ,
AIDA.ASSETS_TRACKING_FLAG ,
AIDA.DISTRIBUTION_LINE_NUMBER ,
AIDA.LINE_TYPE_LOOKUP_CODE ,
AIDA.PO_DISTRIBUTION_ID ,
AIDA.BASE_AMOUNT ,
AIDA.EXCHANGE_RATE ,
AIDA.EXCHANGE_RATE_TYPE ,
AIDA.EXCHANGE_DATE ,
PA_ADDITION_FLAG ,
AIDA.JE_BATCH_ID ,
AIDA.POSTED_AMOUNT ,
AIDA.POSTED_BASE_AMOUNT ,
AIDA.ENCUMBERED_FLAG ,
AIDA.ACCRUAL_POSTED_FLAG ,
AIDA.CASH_POSTED_FLAG ,
AIDA.LAST_UPDATE_LOGIN ,
CASH_JE_BATCH_ID ,
AIDA.STAT_AMOUNT ,
AIDA.ATTRIBUTE11 ,
AIDA.ATTRIBUTE12 ,
AIDA.ATTRIBUTE13 ,
AIDA.ATTRIBUTE14 ,
AIDA.ATTRIBUTE6 ,
AIDA.ATTRIBUTE7 ,
AIDA.ATTRIBUTE8 ,
AIDA.ATTRIBUTE9 ,
AIDA.ATTRIBUTE10 ,
AIDA.ATTRIBUTE15 ,
AIDA.ACCTS_PAY_CODE_COMBINATION_ID ,
RATE_VAR_CODE_COMBINATION_ID ,
PRICE_VAR_CODE_COMBINATION_ID ,
EXCHANGE_RATE_VARIANCE ,
INVOICE_PRICE_VARIANCE ,
BASE_INVOICE_PRICE_VARIANCE ,
REVERSAL_FLAG ,
AIDA.PARENT_INVOICE_ID ,
AIDA.INCOME_TAX_REGION ,
AIDA.FINAL_MATCH_FLAG ,
AIDA.USSGL_TRANSACTION_CODE ,
AIDA.USSGL_TRX_CODE_CONTEXT ,
AIDA.EXPENDITURE_ITEM_DATE ,
AIDA.EXPENDITURE_ORGANIZATION_ID ,
AIDA.EXPENDITURE_TYPE ,
AIDA.PA_QUANTITY ,
AIDA.PROJECT_ID ,
AIDA.TASK_ID ,
AIDA.PROJECT_ACCOUNTING_CONTEXT ,
QUANTITY_VARIANCE ,
BASE_QUANTITY_VARIANCE ,
PACKET_ID ,
AIDA.AWT_FLAG,
AIDA.AWT_GROUP_ID ,
AWT_TAX_RATE_ID ,
AWT_GROSS_AMOUNT ,
AIDA.REFERENCE_1 ,
AIDA.REFERENCE_2 ,
AIDA.ORG_ID ,
OTHER_INVOICE_ID ,
AWT_INVOICE_ID ,
AWT_ORIGIN_GROUP_ID ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
PROGRAM_UPDATE_DATE ,
REQUEST_ID ,
AMOUNT_INCLUDES_TAX_FLAG ,
TAX_CODE_OVERRIDE_FLAG ,
TAX_RECOVERY_RATE ,
TAX_RECOVERY_OVERRIDE_FLAG ,
TAX_RECOVERABLE_FLAG ,
AIDA.AWARD_ID,
START_EXPENSE_DATE ,
MERCHANT_DOCUMENT_NUMBER ,
MERCHANT_NAME ,
MERCHANT_TAX_REG_NUMBER ,
MERCHANT_TAXPAYER_ID ,
COUNTRY_OF_SUPPLY ,
MERCHANT_REFERENCE ,
ACCOUNTING_EVENT_ID
FROM AP_INVOICES_ALL AIA,
AP_INVOICE_DISTRIBUTIONS_ALL AIDA
WHERE AIA.INVOICE_NUM in ('332766')
AND AIDA.POSTED_FLAG = 'N'
AND DISTRIBUTION_LINE_NUMBER in (5)
AND AIA.INVOICE_ID = AIDA.INVOICE_ID FOR UPDATE;
V_USER_ID NUMBER;
V_RESP_ID NUMBER;
V_RESP_APPL_ID NUMBER;
BEGIN
FND_PROFILE.GET('USER_ID',V_USER_ID);
FND_PROFILE.GET('RESP_ID',V_RESP_ID);
FND_PROFILE.GET('RESP_APPL_ID',V_RESP_APPL_ID);
FND_GLOBAL.APPS_INITIALIZE(V_USER_ID,V_RESP_ID,V_RESP_APPL_ID);
FOR REC IN CUR LOOP
BEGIN
V_ACCOUNTING_DATE := '01-AUG-13';
AP_AID_TABLE_HANDLER_PKG.UPDATE_ROW
(P_ROWID => REC.ROWID ,
P_INVOICE_ID => REC.INVOICE_ID ,
P_DIST_CODE_COMBINATION_ID => REC.DIST_CODE_COMBINATION_ID ,
P_LAST_UPDATE_DATE => REC.LAST_UPDATE_DATE ,
P_LAST_UPDATED_BY => REC.LAST_UPDATED_BY ,
P_ACCOUNTING_DATE => V_ACCOUNTING_DATE,--REC.ACCOUNTING_DATE ,
P_PERIOD_NAME => REC.PERIOD_NAME,
P_SET_OF_BOOKS_ID => REC.SET_OF_BOOKS_ID ,
P_AMOUNT => REC.AMOUNT ,
P_DESCRIPTION => REC.DESCRIPTION ,
P_TYPE_1099 => REC.TYPE_1099 ,
P_TAX_CODE_ID => REC.TAX_CODE_ID,
P_POSTED_FLAG => REC.POSTED_FLAG ,
P_BATCH_ID => REC.BATCH_ID,
P_QUANTITY_INVOICED => REC.QUANTITY_INVOICED,
P_UNIT_PRICE => REC.UNIT_PRICE ,
P_MATCH_STATUS_FLAG => REC.MATCH_STATUS_FLAG ,
P_ATTRIBUTE_CATEGORY => REC.ATTRIBUTE_CATEGORY ,
P_ATTRIBUTE1 => REC.ATTRIBUTE1 ,
P_ATTRIBUTE2 => REC.ATTRIBUTE2 ,
P_ATTRIBUTE3 => REC.ATTRIBUTE3 ,
P_ATTRIBUTE4 => REC.ATTRIBUTE4 ,
P_ATTRIBUTE5 => REC.ATTRIBUTE5 ,
P_PREPAY_AMOUNT_REMAINING => REC.PREPAY_AMOUNT_REMAINING ,
P_ASSETS_ADDITION_FLAG => REC.ASSETS_ADDITION_FLAG ,
P_ASSETS_TRACKING_FLAG => REC.ASSETS_TRACKING_FLAG ,
P_DISTRIBUTION_LINE_NUMBER => REC.DISTRIBUTION_LINE_NUMBER ,
P_LINE_TYPE_LOOKUP_CODE => REC.LINE_TYPE_LOOKUP_CODE ,
P_PO_DISTRIBUTION_ID => REC.PO_DISTRIBUTION_ID ,
P_BASE_AMOUNT => REC.BASE_AMOUNT ,
P_EXCHANGE_RATE => REC.EXCHANGE_RATE ,
P_EXCHANGE_RATE_TYPE => REC.EXCHANGE_RATE_TYPE ,
P_EXCHANGE_DATE => REC.EXCHANGE_DATE ,
P_PA_ADDITION_FLAG => REC.PA_ADDITION_FLAG ,
P_JE_BATCH_ID => REC.JE_BATCH_ID ,
P_POSTED_AMOUNT => REC.POSTED_AMOUNT ,
P_POSTED_BASE_AMOUNT => REC.POSTED_BASE_AMOUNT ,
P_ENCUMBERED_FLAG => REC.ENCUMBERED_FLAG ,
P_ACCRUAL_POSTED_FLAG => REC.ACCRUAL_POSTED_FLAG ,
P_CASH_POSTED_FLAG => REC.CASH_POSTED_FLAG ,
P_LAST_UPDATE_LOGIN => REC.LAST_UPDATE_LOGIN ,
P_CASH_JE_BATCH_ID => REC.CASH_JE_BATCH_ID ,
P_STAT_AMOUNT => REC.STAT_AMOUNT ,
P_ATTRIBUTE11 => REC.ATTRIBUTE11,
P_ATTRIBUTE12 => REC.ATTRIBUTE12 ,
P_ATTRIBUTE13 => REC.ATTRIBUTE13 ,
P_ATTRIBUTE14 => REC.ATTRIBUTE14 ,
P_ATTRIBUTE6 => REC.ATTRIBUTE6 ,
P_ATTRIBUTE7 => REC.ATTRIBUTE7 ,
P_ATTRIBUTE8 => REC.ATTRIBUTE8 ,
P_ATTRIBUTE9 => REC.ATTRIBUTE9 ,
P_ATTRIBUTE10 => REC.ATTRIBUTE10 ,
P_ATTRIBUTE15 => REC.ATTRIBUTE15 ,
P_ACCTS_PAY_CODE_COMB_ID => REC.ACCTS_PAY_CODE_COMBINATION_ID,
P_RATE_VAR_CODE_COMBINATION_ID => REC.RATE_VAR_CODE_COMBINATION_ID ,
P_PRICE_VAR_CODE_COMB_ID => REC.PRICE_VAR_CODE_COMBINATION_ID ,
P_EXCHANGE_RATE_VARIANCE => REC.EXCHANGE_RATE_VARIANCE ,
P_INVOICE_PRICE_VARIANCE => REC.INVOICE_PRICE_VARIANCE ,
P_BASE_INVOICE_PRICE_VARIANCE => REC.BASE_INVOICE_PRICE_VARIANCE ,
P_REVERSAL_FLAG => REC.REVERSAL_FLAG ,
P_PARENT_INVOICE_ID => REC.PARENT_INVOICE_ID ,
P_INCOME_TAX_REGION => REC.INCOME_TAX_REGION ,
P_FINAL_MATCH_FLAG => REC.FINAL_MATCH_FLAG ,
P_USSGL_TRANSACTION_CODE => REC.USSGL_TRANSACTION_CODE,
P_USSGL_TRX_CODE_CONTEXT => REC.USSGL_TRX_CODE_CONTEXT ,
P_EXPENDITURE_ITEM_DATE => REC.EXPENDITURE_ITEM_DATE ,
P_EXPENDITURE_ORGANIZATION_ID => REC.EXPENDITURE_ORGANIZATION_ID ,
P_EXPENDITURE_TYPE => REC.EXPENDITURE_TYPE ,
P_PA_QUANTITY => REC.PA_QUANTITY ,
P_PROJECT_ID => REC.PROJECT_ID ,
P_TASK_ID => REC.TASK_ID ,
P_PROJECT_ACCOUNTING_CONTEXT => REC.PROJECT_ACCOUNTING_CONTEXT ,
P_QUANTITY_VARIANCE => REC.QUANTITY_VARIANCE ,
P_BASE_QUANTITY_VARIANCE => REC.BASE_QUANTITY_VARIANCE,
P_PACKET_ID => REC.PACKET_ID ,
P_AWT_FLAG => REC.AWT_FLAG ,
P_AWT_GROUP_ID => REC.AWT_GROUP_ID ,
P_AWT_TAX_RATE_ID => REC.AWT_TAX_RATE_ID ,
P_AWT_GROSS_AMOUNT => REC.AWT_GROSS_AMOUNT ,
P_REFERENCE_1 => REC.REFERENCE_1 ,
P_REFERENCE_2 => REC.REFERENCE_2 ,
P_ORG_ID => REC.ORG_ID ,
P_OTHER_INVOICE_ID => REC.OTHER_INVOICE_ID,
P_AWT_INVOICE_ID => REC.AWT_INVOICE_ID ,
P_AWT_ORIGIN_GROUP_ID => REC.AWT_ORIGIN_GROUP_ID ,
P_PROGRAM_APPLICATION_ID => REC.PROGRAM_APPLICATION_ID,
P_PROGRAM_ID => REC.PROGRAM_ID ,
P_PROGRAM_UPDATE_DATE => REC.PROGRAM_UPDATE_DATE ,
P_REQUEST_ID => REC.REQUEST_ID ,
P_AMOUNT_INCLUDES_TAX_FLAG => REC.AMOUNT_INCLUDES_TAX_FLAG ,
P_TAX_CODE_OVERRIDE_FLAG => REC.TAX_CODE_OVERRIDE_FLAG ,
P_TAX_RECOVERY_RATE => REC.TAX_RECOVERY_RATE ,
P_TAX_RECOVERY_OVERRIDE_FLAG => REC.TAX_RECOVERY_OVERRIDE_FLAG ,
P_TAX_RECOVERABLE_FLAG => REC.TAX_RECOVERABLE_FLAG,
P_AWARD_ID => REC.AWARD_ID ,
P_START_EXPENSE_DATE => REC.START_EXPENSE_DATE ,
P_MERCHANT_DOCUMENT_NUMBER => REC.MERCHANT_DOCUMENT_NUMBER,
P_MERCHANT_NAME => REC.MERCHANT_NAME ,
P_MERCHANT_TAX_REG_NUMBER => REC.MERCHANT_TAX_REG_NUMBER ,
P_MERCHANT_TAXPAYER_ID => REC.MERCHANT_TAXPAYER_ID ,
P_COUNTRY_OF_SUPPLY => REC.COUNTRY_OF_SUPPLY ,
P_MERCHANT_REFERENCE => REC.MERCHANT_REFERENCE ,
P_CALLING_SEQUENCE => 2);
UPDATE ap_accounting_events_all
SET accounting_date = '01-AUG-2013'
WHERE accounting_event_id = REC.ACCOUNTING_EVENT_ID
AND event_status_code IN ('CREATED')
AND org_id = REC.ORG_ID
AND source_id = REC.INVOICE_ID;
v_count := cur%rowcount ;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error Occured while updating the AP Invoice DIstrbution :'||SQLCODE||'-'||SQLERRM);
END;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Total '||v_count||' Line rows got updated');
COMMIT;
end UPDATE;
end UPDATE_PKG;
/
No comments:
Post a Comment