[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/*UPDATE ap_invoice_lines_all
SET invoice_id = pn_invoice_id
WHERE invoice_id = pn_invoice_id;*/ -- for bug 14681650 by anupgupt
SELECT
NVL(MAX (line_number), 0)
INTO
ln_max_line_number
FROM
ap_invoice_lines_all
WHERE invoice_id = pn_invoice_id;
UPDATE
jai_cmn_document_taxes
SET
source_doc_id = pn_invoice_id
WHERE source_doc_id = pn_invoice_id
AND source_doc_type = jai_constants.g_ap_standalone_invoice;
SELECT
NVL(MAX(source_doc_line_id), 0)
INTO
ln_max_line_number
FROM
jai_cmn_document_taxes
WHERE source_doc_id = pn_invoice_id
AND source_doc_type = jai_constants.g_ap_standalone_invoice;
SELECT
account_type
FROM
gl_code_combinations
WHERE code_combination_id = pn_code_combination_id;
REM Purpose : Update CCID fro reverse charge service tax line
REM when org_id or location changed
REM
REM TDD Reference :
REM
REM Assumptions :
REM
REM Parameter IN/OUT Type Required Description and Purpose
REM ----------------------- ------ ------------------ ---------- ------------------------------------
REM pn_invoice_id IN NUMBER Y invoice id.
REM pn_line_number IN NUMBER Y invoice line number
REM pn_org_id IN NUMBER Y organization ID
REM pn_location_id IN NUMBER Y location ID
REM
REM CALLED BY
REM JAI_AP_STND_TAX_PROCESS.UPDATE_CCID
REM +=====================================================================================================+
*/
PROCEDURE UPDATE_CCID(pn_invoice_id IN NUMBER,
pn_line_number IN NUMBER,
pn_org_id IN NUMBER,
pn_location_id IN NUMBER) IS
ln_chargeble_acct_ccid NUMBER;
SELECT ap_dist.invoice_distribution_id, ap_dist.amount, to_number(ap_line.REFERENCE_KEY1) tax_id
FROM AP_INVOICE_LINES_ALL ap_line,
AP_INVOICE_DISTRIBUTIONS_ALL ap_dist
WHERE ap_dist.invoice_id = pn_invoice_id
AND ap_line.invoice_id = ap_dist.invoice_id
AND ap_dist.invoice_line_number = ap_line.line_number
AND ap_line.line_type_lookup_code = 'MISCELLANEOUS'
AND ap_line.application_id = 7000
AND ap_line.PRODUCT_TABLE = 'JAI_CMN_DOCUMENT_TAXES'
AND ap_line.REFERENCE_KEY2 = to_char(pn_line_number);
SELECT
tax_name,
tax_account_id,
mod_cr_percentage,
adhoc_flag,
NVL (tax_rate, -1) tax_rate,
tax_type,
NVL(rounding_factor,0) rounding_factor
FROM jai_cmn_taxes_all
WHERE tax_id = pn_tax_id;
SELECT modvat_flag
FROM JAI_CMN_DOCUMENT_TAXES a,
AP_INVOICE_DISTRIBUTIONS_ALL d
WHERE d.invoice_distribution_id = cn_dist_id
AND a.source_doc_id = pn_invoice_id
AND d.invoice_line_number = a.source_doc_line_id;
in payables invoices. Added the cursor "get_assets_tracking_flag" so that asset_tracking_flag value will be fetched from the cursor instead of fetching it from SELECT query directly */
CURSOR get_assets_tracking_flag
IS
SELECT assets_tracking_flag
FROM ap_invoice_distributions_all
WHERE invoice_id = pn_invoice_id
AND invoice_line_number = pn_line_number
AND distribution_line_number = (select min(distribution_line_number) from ap_invoice_distributions_all
where invoice_id = pn_invoice_id AND invoice_line_number = pn_line_number) ; /* Bug 13617527 */
l_api_name CONSTANT VARCHAR2(50) := 'UPDATE_CCID';
UPDATE ap_invoice_distributions_all
SET dist_code_combination_id = ln_dist_acct_ccid
WHERE invoice_distribution_id = r_jai_cmn_document_taxes.invoice_distribution_id;
END UPDATE_CCID;
SELECT ship_to_organization_id,
ship_to_location_id
FROM po_line_locations_all
WHERE line_location_id IN ( SELECT line_location_id
FROM po_distributions_all
WHERE po_distribution_id = po_dist_id) ;
SELECT
dist_code_combination_id
FROM
ap_invoice_distributions_all
WHERE invoice_id = pn_invoice_id
AND invoice_line_number = pn_item_line_number
AND distribution_line_number =1;
SELECT
regime_id
FROM
jai_rgm_definitions
WHERE regime_code = pv_regime_code;
SELECT
attribute_code tax_type
FROM
jai_rgm_registrations
WHERE regime_id = pn_regime_id
AND registration_type =jai_constants.regn_type_tax_types --tax type
AND attribute_code = pv_tax_type_code;
SELECT
TO_NUMBER (accnts.attribute_value)
FROM
jai_rgm_registrations tax_types
, jai_rgm_registrations accnts
WHERE tax_types.regime_id = pn_regime_id
AND tax_types.registration_type = jai_constants.regn_type_tax_types
AND tax_types.attribute_code = pn_tax_type
AND accnts.regime_id = tax_types.regime_id
AND accnts.registration_type = jai_constants.regn_type_accounts
AND accnts.parent_registration_id = tax_types.registration_id
AND accnts.attribute_code = pv_ac_type;
Select default_dist_ccid
FROM AP_INVOICE_LINES_ALL
WHERE invoice_id = pn_invoice_id
and line_number = pn_item_line_number;
PROCEDURE Insert_jai_inv_line(
p_jai_ap_invoice_lines_rec IN jai_ap_invoice_lines%ROWTYPE
)IS
l_api_name CONSTANT VARCHAR2(50) := 'Insert_jai_inv_line()';
INSERT INTO jai_ap_invoice_lines
(JAI_AP_INVOICE_LINES_ID
,ORGANIZATION_ID
,LOCATION_ID
,INVOICE_ID
,INVOICE_LINE_NUMBER
,SUPPLIER_SITE_ID
,PARENT_INVOICE_LINE_NUMBER
,TAX_CATEGORY_ID
,SERVICE_TYPE_CODE
,MATCH_TYPE
,CURRENCY_CODE
,LINE_AMOUNT
,LINE_TYPE_LOOKUP_CODE
,CREATED_BY
,CREATION_DATE
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,LAST_UPDATED_BY
)
VALUES
(p_jai_ap_invoice_lines_rec.JAI_AP_INVOICE_LINES_ID
,p_jai_ap_invoice_lines_rec.ORGANIZATION_ID
,p_jai_ap_invoice_lines_rec.LOCATION_ID
,p_jai_ap_invoice_lines_rec.INVOICE_ID
,p_jai_ap_invoice_lines_rec.INVOICE_LINE_NUMBER
,p_jai_ap_invoice_lines_rec.SUPPLIER_SITE_ID
,p_jai_ap_invoice_lines_rec.PARENT_INVOICE_LINE_NUMBER
,p_jai_ap_invoice_lines_rec.TAX_CATEGORY_ID
,p_jai_ap_invoice_lines_rec.SERVICE_TYPE_CODE
,p_jai_ap_invoice_lines_rec.MATCH_TYPE
,p_jai_ap_invoice_lines_rec.CURRENCY_CODE
,p_jai_ap_invoice_lines_rec.LINE_AMOUNT
,p_jai_ap_invoice_lines_rec.LINE_TYPE_LOOKUP_CODE
,p_jai_ap_invoice_lines_rec.CREATED_BY
,p_jai_ap_invoice_lines_rec.CREATION_DATE
,p_jai_ap_invoice_lines_rec.LAST_UPDATE_DATE
,p_jai_ap_invoice_lines_rec.LAST_UPDATE_LOGIN
,p_jai_ap_invoice_lines_rec.LAST_UPDATED_BY
);
END Insert_jai_inv_line;
PROCEDURE Insert_ap_inv_line(
p_ap_invoice_lines_all_rec IN ap_invoice_lines_all%ROWTYPE
)IS
l_api_name CONSTANT VARCHAR2(50) := 'Insert_ap_inv_line()';
INSERT INTO ap_invoice_lines_all
(INVOICE_ID
,LINE_NUMBER
,LINE_TYPE_LOOKUP_CODE
,REQUESTER_ID
,DESCRIPTION
,LINE_SOURCE
,ORG_ID
,LINE_GROUP_NUMBER
,INVENTORY_ITEM_ID
,ITEM_DESCRIPTION
,SERIAL_NUMBER
,MANUFACTURER
,MODEL_NUMBER
,WARRANTY_NUMBER
,GENERATE_DISTS
,MATCH_TYPE
,DISTRIBUTION_SET_ID
,ACCOUNT_SEGMENT
,BALANCING_SEGMENT
,COST_CENTER_SEGMENT
,OVERLAY_DIST_CODE_CONCAT
,DEFAULT_DIST_CCID
,PRORATE_ACROSS_ALL_ITEMS
,ACCOUNTING_DATE
,PERIOD_NAME
,DEFERRED_ACCTG_FLAG
,DEF_ACCTG_START_DATE
,DEF_ACCTG_END_DATE
,DEF_ACCTG_NUMBER_OF_PERIODS
,DEF_ACCTG_PERIOD_TYPE
,SET_OF_BOOKS_ID
,AMOUNT
,BASE_AMOUNT
,ROUNDING_AMT
,QUANTITY_INVOICED
,UNIT_MEAS_LOOKUP_CODE
,UNIT_PRICE
,WFAPPROVAL_STATUS
,USSGL_TRANSACTION_CODE
,DISCARDED_FLAG
,ORIGINAL_AMOUNT
,ORIGINAL_BASE_AMOUNT
,ORIGINAL_ROUNDING_AMT
,CANCELLED_FLAG
,INCOME_TAX_REGION
,TYPE_1099
,STAT_AMOUNT
,PREPAY_INVOICE_ID
,PREPAY_LINE_NUMBER
,INVOICE_INCLUDES_PREPAY_FLAG
,CORRECTED_INV_ID
,CORRECTED_LINE_NUMBER
,PO_HEADER_ID
,PO_LINE_ID
,PO_RELEASE_ID
,PO_LINE_LOCATION_ID
,PO_DISTRIBUTION_ID
,RCV_TRANSACTION_ID
,FINAL_MATCH_FLAG
,ASSETS_TRACKING_FLAG
,ASSET_BOOK_TYPE_CODE
,ASSET_CATEGORY_ID
,PROJECT_ID
,TASK_ID
,EXPENDITURE_TYPE
,EXPENDITURE_ITEM_DATE
,EXPENDITURE_ORGANIZATION_ID
,PA_QUANTITY
,PA_CC_AR_INVOICE_ID
,PA_CC_AR_INVOICE_LINE_NUM
,PA_CC_PROCESSED_CODE
,AWARD_ID
,AWT_GROUP_ID
,REFERENCE_1
,REFERENCE_2
,RECEIPT_VERIFIED_FLAG
,RECEIPT_REQUIRED_FLAG
,RECEIPT_MISSING_FLAG
,JUSTIFICATION
,EXPENSE_GROUP
,START_EXPENSE_DATE
,END_EXPENSE_DATE
,RECEIPT_CURRENCY_CODE
,RECEIPT_CONVERSION_RATE
,RECEIPT_CURRENCY_AMOUNT
,DAILY_AMOUNT
,WEB_PARAMETER_ID
,ADJUSTMENT_REASON
,MERCHANT_DOCUMENT_NUMBER
,MERCHANT_NAME
,MERCHANT_REFERENCE
,MERCHANT_TAX_REG_NUMBER
,MERCHANT_TAXPAYER_ID
,COUNTRY_OF_SUPPLY
,CREDIT_CARD_TRX_ID
,COMPANY_PREPAID_INVOICE_ID
,CC_REVERSAL_FLAG
,CREATION_DATE
,CREATED_BY
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,REQUEST_ID
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,GLOBAL_ATTRIBUTE_CATEGORY
,GLOBAL_ATTRIBUTE1
,GLOBAL_ATTRIBUTE2
,GLOBAL_ATTRIBUTE3
,GLOBAL_ATTRIBUTE4
,GLOBAL_ATTRIBUTE5
,GLOBAL_ATTRIBUTE6
,GLOBAL_ATTRIBUTE7
,GLOBAL_ATTRIBUTE8
,GLOBAL_ATTRIBUTE9
,GLOBAL_ATTRIBUTE10
,GLOBAL_ATTRIBUTE11
,GLOBAL_ATTRIBUTE12
,GLOBAL_ATTRIBUTE13
,GLOBAL_ATTRIBUTE14
,GLOBAL_ATTRIBUTE15
,GLOBAL_ATTRIBUTE16
,GLOBAL_ATTRIBUTE17
,GLOBAL_ATTRIBUTE18
,GLOBAL_ATTRIBUTE19
,GLOBAL_ATTRIBUTE20
,LINE_SELECTED_FOR_APPL_FLAG
,PREPAY_APPL_REQUEST_ID
,APPLICATION_ID
,PRODUCT_TABLE
,REFERENCE_KEY1
,REFERENCE_KEY2
,REFERENCE_KEY3
,REFERENCE_KEY4
,REFERENCE_KEY5
,PURCHASING_CATEGORY_ID
,COST_FACTOR_ID
,CONTROL_AMOUNT
,ASSESSABLE_VALUE
,TOTAL_REC_TAX_AMOUNT
,TOTAL_NREC_TAX_AMOUNT
,TOTAL_REC_TAX_AMT_FUNCL_CURR
,TOTAL_NREC_TAX_AMT_FUNCL_CURR
,INCLUDED_TAX_AMOUNT
,PRIMARY_INTENDED_USE
,TAX_ALREADY_CALCULATED_FLAG
,SHIP_TO_LOCATION_ID
,PRODUCT_TYPE
,PRODUCT_CATEGORY
,PRODUCT_FISC_CLASSIFICATION
,USER_DEFINED_FISC_CLASS
,TRX_BUSINESS_CATEGORY
,SUMMARY_TAX_LINE_ID
,TAX_REGIME_CODE
,TAX
,TAX_JURISDICTION_CODE
,TAX_STATUS_CODE
,TAX_RATE_ID
,TAX_RATE_CODE
,TAX_RATE
,TAX_CODE_ID
,HISTORICAL_FLAG
,TAX_CLASSIFICATION_CODE
,SOURCE_APPLICATION_ID
,SOURCE_EVENT_CLASS_CODE
,SOURCE_ENTITY_CODE
,SOURCE_TRX_ID
,SOURCE_LINE_ID
,SOURCE_TRX_LEVEL_TYPE
,RETAINED_AMOUNT
,RETAINED_AMOUNT_REMAINING
,RETAINED_INVOICE_ID
,RETAINED_LINE_NUMBER
,LINE_SELECTED_FOR_RELEASE_FLAG
,LINE_OWNER_ROLE
,DISPUTABLE_FLAG
,RCV_SHIPMENT_LINE_ID
,AIL_INVOICE_ID
,AIL_DISTRIBUTION_LINE_NUMBER
,AIL_INVOICE_ID2
,AIL_DISTRIBUTION_LINE_NUMBER2
,AIL_INVOICE_ID3
,AIL_DISTRIBUTION_LINE_NUMBER3
,AIL_INVOICE_ID4
,PAY_AWT_GROUP_ID
)
VALUES
(p_ap_invoice_lines_all_rec.INVOICE_ID
,p_ap_invoice_lines_all_rec.LINE_NUMBER
,p_ap_invoice_lines_all_rec.LINE_TYPE_LOOKUP_CODE
,p_ap_invoice_lines_all_rec.REQUESTER_ID
,p_ap_invoice_lines_all_rec.DESCRIPTION
,p_ap_invoice_lines_all_rec.LINE_SOURCE
,p_ap_invoice_lines_all_rec.ORG_ID
,p_ap_invoice_lines_all_rec.LINE_GROUP_NUMBER
,p_ap_invoice_lines_all_rec.INVENTORY_ITEM_ID
,p_ap_invoice_lines_all_rec.ITEM_DESCRIPTION
,p_ap_invoice_lines_all_rec.SERIAL_NUMBER
,p_ap_invoice_lines_all_rec.MANUFACTURER
,p_ap_invoice_lines_all_rec.MODEL_NUMBER
,p_ap_invoice_lines_all_rec.WARRANTY_NUMBER
,p_ap_invoice_lines_all_rec.GENERATE_DISTS
,p_ap_invoice_lines_all_rec.MATCH_TYPE
,p_ap_invoice_lines_all_rec.DISTRIBUTION_SET_ID
,p_ap_invoice_lines_all_rec.ACCOUNT_SEGMENT
,p_ap_invoice_lines_all_rec.BALANCING_SEGMENT
,p_ap_invoice_lines_all_rec.COST_CENTER_SEGMENT
,p_ap_invoice_lines_all_rec.OVERLAY_DIST_CODE_CONCAT
,p_ap_invoice_lines_all_rec.DEFAULT_DIST_CCID
,p_ap_invoice_lines_all_rec.PRORATE_ACROSS_ALL_ITEMS
,p_ap_invoice_lines_all_rec.ACCOUNTING_DATE
,p_ap_invoice_lines_all_rec.PERIOD_NAME
,p_ap_invoice_lines_all_rec.DEFERRED_ACCTG_FLAG
,p_ap_invoice_lines_all_rec.DEF_ACCTG_START_DATE
,p_ap_invoice_lines_all_rec.DEF_ACCTG_END_DATE
,p_ap_invoice_lines_all_rec.DEF_ACCTG_NUMBER_OF_PERIODS
,p_ap_invoice_lines_all_rec.DEF_ACCTG_PERIOD_TYPE
,p_ap_invoice_lines_all_rec.SET_OF_BOOKS_ID
,p_ap_invoice_lines_all_rec.AMOUNT
,p_ap_invoice_lines_all_rec.BASE_AMOUNT
,p_ap_invoice_lines_all_rec.ROUNDING_AMT
,p_ap_invoice_lines_all_rec.QUANTITY_INVOICED
,p_ap_invoice_lines_all_rec.UNIT_MEAS_LOOKUP_CODE
,p_ap_invoice_lines_all_rec.UNIT_PRICE
,p_ap_invoice_lines_all_rec.WFAPPROVAL_STATUS
,p_ap_invoice_lines_all_rec.USSGL_TRANSACTION_CODE
,p_ap_invoice_lines_all_rec.DISCARDED_FLAG
,p_ap_invoice_lines_all_rec.ORIGINAL_AMOUNT
,p_ap_invoice_lines_all_rec.ORIGINAL_BASE_AMOUNT
,p_ap_invoice_lines_all_rec.ORIGINAL_ROUNDING_AMT
,p_ap_invoice_lines_all_rec.CANCELLED_FLAG
,p_ap_invoice_lines_all_rec.INCOME_TAX_REGION
,p_ap_invoice_lines_all_rec.TYPE_1099
,p_ap_invoice_lines_all_rec.STAT_AMOUNT
,p_ap_invoice_lines_all_rec.PREPAY_INVOICE_ID
,p_ap_invoice_lines_all_rec.PREPAY_LINE_NUMBER
,p_ap_invoice_lines_all_rec.INVOICE_INCLUDES_PREPAY_FLAG
,p_ap_invoice_lines_all_rec.CORRECTED_INV_ID
,p_ap_invoice_lines_all_rec.CORRECTED_LINE_NUMBER
,p_ap_invoice_lines_all_rec.PO_HEADER_ID
,p_ap_invoice_lines_all_rec.PO_LINE_ID
,p_ap_invoice_lines_all_rec.PO_RELEASE_ID
,p_ap_invoice_lines_all_rec.PO_LINE_LOCATION_ID
,p_ap_invoice_lines_all_rec.PO_DISTRIBUTION_ID
,p_ap_invoice_lines_all_rec.RCV_TRANSACTION_ID
,p_ap_invoice_lines_all_rec.FINAL_MATCH_FLAG
,p_ap_invoice_lines_all_rec.ASSETS_TRACKING_FLAG
,p_ap_invoice_lines_all_rec.ASSET_BOOK_TYPE_CODE
,p_ap_invoice_lines_all_rec.ASSET_CATEGORY_ID
,p_ap_invoice_lines_all_rec.PROJECT_ID
,p_ap_invoice_lines_all_rec.TASK_ID
,p_ap_invoice_lines_all_rec.EXPENDITURE_TYPE
,p_ap_invoice_lines_all_rec.EXPENDITURE_ITEM_DATE
,p_ap_invoice_lines_all_rec.EXPENDITURE_ORGANIZATION_ID
,p_ap_invoice_lines_all_rec.PA_QUANTITY
,p_ap_invoice_lines_all_rec.PA_CC_AR_INVOICE_ID
,p_ap_invoice_lines_all_rec.PA_CC_AR_INVOICE_LINE_NUM
,p_ap_invoice_lines_all_rec.PA_CC_PROCESSED_CODE
,p_ap_invoice_lines_all_rec.AWARD_ID
,p_ap_invoice_lines_all_rec.AWT_GROUP_ID
,p_ap_invoice_lines_all_rec.REFERENCE_1
,p_ap_invoice_lines_all_rec.REFERENCE_2
,p_ap_invoice_lines_all_rec.RECEIPT_VERIFIED_FLAG
,p_ap_invoice_lines_all_rec.RECEIPT_REQUIRED_FLAG
,p_ap_invoice_lines_all_rec.RECEIPT_MISSING_FLAG
,p_ap_invoice_lines_all_rec.JUSTIFICATION
,p_ap_invoice_lines_all_rec.EXPENSE_GROUP
,p_ap_invoice_lines_all_rec.START_EXPENSE_DATE
,p_ap_invoice_lines_all_rec.END_EXPENSE_DATE
,p_ap_invoice_lines_all_rec.RECEIPT_CURRENCY_CODE
,p_ap_invoice_lines_all_rec.RECEIPT_CONVERSION_RATE
,p_ap_invoice_lines_all_rec.RECEIPT_CURRENCY_AMOUNT
,p_ap_invoice_lines_all_rec.DAILY_AMOUNT
,p_ap_invoice_lines_all_rec.WEB_PARAMETER_ID
,p_ap_invoice_lines_all_rec.ADJUSTMENT_REASON
,p_ap_invoice_lines_all_rec.MERCHANT_DOCUMENT_NUMBER
,p_ap_invoice_lines_all_rec.MERCHANT_NAME
,p_ap_invoice_lines_all_rec.MERCHANT_REFERENCE
,p_ap_invoice_lines_all_rec.MERCHANT_TAX_REG_NUMBER
,p_ap_invoice_lines_all_rec.MERCHANT_TAXPAYER_ID
,p_ap_invoice_lines_all_rec.COUNTRY_OF_SUPPLY
,p_ap_invoice_lines_all_rec.CREDIT_CARD_TRX_ID
,p_ap_invoice_lines_all_rec.COMPANY_PREPAID_INVOICE_ID
,p_ap_invoice_lines_all_rec.CC_REVERSAL_FLAG
,p_ap_invoice_lines_all_rec.CREATION_DATE
,p_ap_invoice_lines_all_rec.CREATED_BY
,p_ap_invoice_lines_all_rec.LAST_UPDATED_BY
,p_ap_invoice_lines_all_rec.LAST_UPDATE_DATE
,p_ap_invoice_lines_all_rec.LAST_UPDATE_LOGIN
,p_ap_invoice_lines_all_rec.PROGRAM_APPLICATION_ID
,p_ap_invoice_lines_all_rec.PROGRAM_ID
,p_ap_invoice_lines_all_rec.PROGRAM_UPDATE_DATE
,p_ap_invoice_lines_all_rec.REQUEST_ID
,p_ap_invoice_lines_all_rec.ATTRIBUTE_CATEGORY
,p_ap_invoice_lines_all_rec.ATTRIBUTE1
,p_ap_invoice_lines_all_rec.ATTRIBUTE2
,p_ap_invoice_lines_all_rec.ATTRIBUTE3
,p_ap_invoice_lines_all_rec.ATTRIBUTE4
,p_ap_invoice_lines_all_rec.ATTRIBUTE5
,p_ap_invoice_lines_all_rec.ATTRIBUTE6
,p_ap_invoice_lines_all_rec.ATTRIBUTE7
,p_ap_invoice_lines_all_rec.ATTRIBUTE8
,p_ap_invoice_lines_all_rec.ATTRIBUTE9
,p_ap_invoice_lines_all_rec.ATTRIBUTE10
,p_ap_invoice_lines_all_rec.ATTRIBUTE11
,p_ap_invoice_lines_all_rec.ATTRIBUTE12
,p_ap_invoice_lines_all_rec.ATTRIBUTE13
,p_ap_invoice_lines_all_rec.ATTRIBUTE14
,p_ap_invoice_lines_all_rec.ATTRIBUTE15
,p_ap_invoice_lines_all_rec.GLOBAL_ATTRIBUTE_CATEGORY
,p_ap_invoice_lines_all_rec.GLOBAL_ATTRIBUTE1
,p_ap_invoice_lines_all_rec.GLOBAL_ATTRIBUTE2
,p_ap_invoice_lines_all_rec.GLOBAL_ATTRIBUTE3
,p_ap_invoice_lines_all_rec.GLOBAL_ATTRIBUTE4
,p_ap_invoice_lines_all_rec.GLOBAL_ATTRIBUTE5
,p_ap_invoice_lines_all_rec.GLOBAL_ATTRIBUTE6
,p_ap_invoice_lines_all_rec.GLOBAL_ATTRIBUTE7
,p_ap_invoice_lines_all_rec.GLOBAL_ATTRIBUTE8
,p_ap_invoice_lines_all_rec.GLOBAL_ATTRIBUTE9
,p_ap_invoice_lines_all_rec.GLOBAL_ATTRIBUTE10
,p_ap_invoice_lines_all_rec.GLOBAL_ATTRIBUTE11
,p_ap_invoice_lines_all_rec.GLOBAL_ATTRIBUTE12
,p_ap_invoice_lines_all_rec.GLOBAL_ATTRIBUTE13
,p_ap_invoice_lines_all_rec.GLOBAL_ATTRIBUTE14
,p_ap_invoice_lines_all_rec.GLOBAL_ATTRIBUTE15
,p_ap_invoice_lines_all_rec.GLOBAL_ATTRIBUTE16
,p_ap_invoice_lines_all_rec.GLOBAL_ATTRIBUTE17
,p_ap_invoice_lines_all_rec.GLOBAL_ATTRIBUTE18
,p_ap_invoice_lines_all_rec.GLOBAL_ATTRIBUTE19
,p_ap_invoice_lines_all_rec.GLOBAL_ATTRIBUTE20
,p_ap_invoice_lines_all_rec.LINE_SELECTED_FOR_APPL_FLAG
,p_ap_invoice_lines_all_rec.PREPAY_APPL_REQUEST_ID
,p_ap_invoice_lines_all_rec.APPLICATION_ID
,p_ap_invoice_lines_all_rec.PRODUCT_TABLE
,p_ap_invoice_lines_all_rec.REFERENCE_KEY1
,p_ap_invoice_lines_all_rec.REFERENCE_KEY2
,p_ap_invoice_lines_all_rec.REFERENCE_KEY3
,p_ap_invoice_lines_all_rec.REFERENCE_KEY4
,p_ap_invoice_lines_all_rec.REFERENCE_KEY5
,p_ap_invoice_lines_all_rec.PURCHASING_CATEGORY_ID
,p_ap_invoice_lines_all_rec.COST_FACTOR_ID
,p_ap_invoice_lines_all_rec.CONTROL_AMOUNT
,p_ap_invoice_lines_all_rec.ASSESSABLE_VALUE
,p_ap_invoice_lines_all_rec.TOTAL_REC_TAX_AMOUNT
,p_ap_invoice_lines_all_rec.TOTAL_NREC_TAX_AMOUNT
,p_ap_invoice_lines_all_rec.TOTAL_REC_TAX_AMT_FUNCL_CURR
,p_ap_invoice_lines_all_rec.TOTAL_NREC_TAX_AMT_FUNCL_CURR
,p_ap_invoice_lines_all_rec.INCLUDED_TAX_AMOUNT
,p_ap_invoice_lines_all_rec.PRIMARY_INTENDED_USE
,p_ap_invoice_lines_all_rec.TAX_ALREADY_CALCULATED_FLAG
,p_ap_invoice_lines_all_rec.SHIP_TO_LOCATION_ID
,p_ap_invoice_lines_all_rec.PRODUCT_TYPE
,p_ap_invoice_lines_all_rec.PRODUCT_CATEGORY
,p_ap_invoice_lines_all_rec.PRODUCT_FISC_CLASSIFICATION
,p_ap_invoice_lines_all_rec.USER_DEFINED_FISC_CLASS
,p_ap_invoice_lines_all_rec.TRX_BUSINESS_CATEGORY
,p_ap_invoice_lines_all_rec.SUMMARY_TAX_LINE_ID
,p_ap_invoice_lines_all_rec.TAX_REGIME_CODE
,p_ap_invoice_lines_all_rec.TAX
,p_ap_invoice_lines_all_rec.TAX_JURISDICTION_CODE
,p_ap_invoice_lines_all_rec.TAX_STATUS_CODE
,p_ap_invoice_lines_all_rec.TAX_RATE_ID
,p_ap_invoice_lines_all_rec.TAX_RATE_CODE
,p_ap_invoice_lines_all_rec.TAX_RATE
,p_ap_invoice_lines_all_rec.TAX_CODE_ID
,p_ap_invoice_lines_all_rec.HISTORICAL_FLAG
,p_ap_invoice_lines_all_rec.TAX_CLASSIFICATION_CODE
,p_ap_invoice_lines_all_rec.SOURCE_APPLICATION_ID
,p_ap_invoice_lines_all_rec.SOURCE_EVENT_CLASS_CODE
,p_ap_invoice_lines_all_rec.SOURCE_ENTITY_CODE
,p_ap_invoice_lines_all_rec.SOURCE_TRX_ID
,p_ap_invoice_lines_all_rec.SOURCE_LINE_ID
,p_ap_invoice_lines_all_rec.SOURCE_TRX_LEVEL_TYPE
,p_ap_invoice_lines_all_rec.RETAINED_AMOUNT
,p_ap_invoice_lines_all_rec.RETAINED_AMOUNT_REMAINING
,p_ap_invoice_lines_all_rec.RETAINED_INVOICE_ID
,p_ap_invoice_lines_all_rec.RETAINED_LINE_NUMBER
,p_ap_invoice_lines_all_rec.LINE_SELECTED_FOR_RELEASE_FLAG
,p_ap_invoice_lines_all_rec.LINE_OWNER_ROLE
,p_ap_invoice_lines_all_rec.DISPUTABLE_FLAG
,p_ap_invoice_lines_all_rec.RCV_SHIPMENT_LINE_ID
,p_ap_invoice_lines_all_rec.AIL_INVOICE_ID
,p_ap_invoice_lines_all_rec.AIL_DISTRIBUTION_LINE_NUMBER
,p_ap_invoice_lines_all_rec.AIL_INVOICE_ID2
,p_ap_invoice_lines_all_rec.AIL_DISTRIBUTION_LINE_NUMBER2
,p_ap_invoice_lines_all_rec.AIL_INVOICE_ID3
,p_ap_invoice_lines_all_rec.AIL_DISTRIBUTION_LINE_NUMBER3
,p_ap_invoice_lines_all_rec.AIL_INVOICE_ID4
,p_ap_invoice_lines_all_rec.PAY_AWT_GROUP_ID
);
END Insert_ap_inv_line;
PROCEDURE Insert_ap_inv_dst_line(
p_ap_invoice_dist_all_rec IN ap_invoice_distributions_all%ROWTYPE
)IS
l_api_name CONSTANT VARCHAR2(50) := 'Insert_ap_inv_dst_line()';
INSERT INTO ap_invoice_distributions_all
(ACCOUNTING_DATE
,ACCRUAL_POSTED_FLAG
,ASSETS_ADDITION_FLAG
,ASSETS_TRACKING_FLAG
,CASH_POSTED_FLAG
,DISTRIBUTION_LINE_NUMBER
,DIST_CODE_COMBINATION_ID
,INVOICE_ID
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LINE_TYPE_LOOKUP_CODE
,PERIOD_NAME
,SET_OF_BOOKS_ID
,ACCTS_PAY_CODE_COMBINATION_ID
,AMOUNT
,BASE_AMOUNT
,BASE_INVOICE_PRICE_VARIANCE
,BATCH_ID
,CREATED_BY
,CREATION_DATE
,DESCRIPTION
,EXCHANGE_RATE_VARIANCE
,FINAL_MATCH_FLAG
,INCOME_TAX_REGION
,INVOICE_PRICE_VARIANCE
,LAST_UPDATE_LOGIN
,MATCH_STATUS_FLAG
,POSTED_FLAG
,PO_DISTRIBUTION_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,QUANTITY_INVOICED
,RATE_VAR_CODE_COMBINATION_ID
,REQUEST_ID
,REVERSAL_FLAG
,TYPE_1099
,UNIT_PRICE
,AMOUNT_ENCUMBERED
,BASE_AMOUNT_ENCUMBERED
,ENCUMBERED_FLAG
,EXCHANGE_DATE
,EXCHANGE_RATE
,EXCHANGE_RATE_TYPE
,PRICE_ADJUSTMENT_FLAG
,PRICE_VAR_CODE_COMBINATION_ID
,QUANTITY_UNENCUMBERED
,STAT_AMOUNT
,AMOUNT_TO_POST
,ATTRIBUTE1
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE_CATEGORY
,BASE_AMOUNT_TO_POST
,CASH_JE_BATCH_ID
,EXPENDITURE_ITEM_DATE
,EXPENDITURE_ORGANIZATION_ID
,EXPENDITURE_TYPE
,JE_BATCH_ID
,PARENT_INVOICE_ID
,PA_ADDITION_FLAG
,PA_QUANTITY
,POSTED_AMOUNT
,POSTED_BASE_AMOUNT
,PREPAY_AMOUNT_REMAINING
,PROJECT_ACCOUNTING_CONTEXT
,PROJECT_ID
,TASK_ID
,USSGL_TRANSACTION_CODE
,USSGL_TRX_CODE_CONTEXT
,EARLIEST_SETTLEMENT_DATE
,REQ_DISTRIBUTION_ID
,QUANTITY_VARIANCE
,BASE_QUANTITY_VARIANCE
,PACKET_ID
,AWT_FLAG
,AWT_GROUP_ID
,AWT_TAX_RATE_ID
,AWT_GROSS_AMOUNT
,AWT_INVOICE_ID
,AWT_ORIGIN_GROUP_ID
,REFERENCE_1
,REFERENCE_2
,ORG_ID
,OTHER_INVOICE_ID
,AWT_INVOICE_PAYMENT_ID
,GLOBAL_ATTRIBUTE_CATEGORY
,GLOBAL_ATTRIBUTE1
,GLOBAL_ATTRIBUTE2
,GLOBAL_ATTRIBUTE3
,GLOBAL_ATTRIBUTE4
,GLOBAL_ATTRIBUTE5
,GLOBAL_ATTRIBUTE6
,GLOBAL_ATTRIBUTE7
,GLOBAL_ATTRIBUTE8
,GLOBAL_ATTRIBUTE9
,GLOBAL_ATTRIBUTE10
,GLOBAL_ATTRIBUTE11
,GLOBAL_ATTRIBUTE12
,GLOBAL_ATTRIBUTE13
,GLOBAL_ATTRIBUTE14
,GLOBAL_ATTRIBUTE15
,GLOBAL_ATTRIBUTE16
,GLOBAL_ATTRIBUTE17
,GLOBAL_ATTRIBUTE18
,GLOBAL_ATTRIBUTE19
,GLOBAL_ATTRIBUTE20
,LINE_GROUP_NUMBER
,RECEIPT_VERIFIED_FLAG
,RECEIPT_REQUIRED_FLAG
,RECEIPT_MISSING_FLAG
,JUSTIFICATION
,EXPENSE_GROUP
,START_EXPENSE_DATE
,END_EXPENSE_DATE
,RECEIPT_CURRENCY_CODE
,RECEIPT_CONVERSION_RATE
,RECEIPT_CURRENCY_AMOUNT
,DAILY_AMOUNT
,WEB_PARAMETER_ID
,ADJUSTMENT_REASON
,AWARD_ID
,MRC_ACCRUAL_POSTED_FLAG
,MRC_CASH_POSTED_FLAG
,MRC_DIST_CODE_COMBINATION_ID
,MRC_AMOUNT
,MRC_BASE_AMOUNT
,MRC_BASE_INV_PRICE_VARIANCE
,MRC_EXCHANGE_RATE_VARIANCE
,MRC_POSTED_FLAG
,MRC_PROGRAM_APPLICATION_ID
,MRC_PROGRAM_ID
,MRC_PROGRAM_UPDATE_DATE
,MRC_RATE_VAR_CCID
,MRC_REQUEST_ID
,MRC_EXCHANGE_DATE
,MRC_EXCHANGE_RATE
,MRC_EXCHANGE_RATE_TYPE
,MRC_AMOUNT_TO_POST
,MRC_BASE_AMOUNT_TO_POST
,MRC_CASH_JE_BATCH_ID
,MRC_JE_BATCH_ID
,MRC_POSTED_AMOUNT
,MRC_POSTED_BASE_AMOUNT
,MRC_RECEIPT_CONVERSION_RATE
,CREDIT_CARD_TRX_ID
,DIST_MATCH_TYPE
,RCV_TRANSACTION_ID
,INVOICE_DISTRIBUTION_ID
,PARENT_REVERSAL_ID
,TAX_RECOVERABLE_FLAG
,PA_CC_AR_INVOICE_ID
,PA_CC_AR_INVOICE_LINE_NUM
,PA_CC_PROCESSED_CODE
,MERCHANT_DOCUMENT_NUMBER
,MERCHANT_NAME
,MERCHANT_REFERENCE
,MERCHANT_TAX_REG_NUMBER
,MERCHANT_TAXPAYER_ID
,COUNTRY_OF_SUPPLY
,MATCHED_UOM_LOOKUP_CODE
,GMS_BURDENABLE_RAW_COST
,ACCOUNTING_EVENT_ID
,PREPAY_DISTRIBUTION_ID
,UPGRADE_POSTED_AMT
,UPGRADE_BASE_POSTED_AMT
,INVENTORY_TRANSFER_STATUS
,COMPANY_PREPAID_INVOICE_ID
,CC_REVERSAL_FLAG
,AWT_WITHHELD_AMT
,INVOICE_INCLUDES_PREPAY_FLAG
,PRICE_CORRECT_INV_ID
,PRICE_CORRECT_QTY
,PA_CMT_XFACE_FLAG
,CANCELLATION_FLAG
,INVOICE_LINE_NUMBER
,CORRECTED_INVOICE_DIST_ID
,ROUNDING_AMT
,CHARGE_APPLICABLE_TO_DIST_ID
,CORRECTED_QUANTITY
,RELATED_ID
,ASSET_BOOK_TYPE_CODE
,ASSET_CATEGORY_ID
,DISTRIBUTION_CLASS
,FINAL_PAYMENT_ROUNDING
,FINAL_APPLICATION_ROUNDING
,AMOUNT_AT_PREPAY_XRATE
,CASH_BASIS_FINAL_APP_ROUNDING
,AMOUNT_AT_PREPAY_PAY_XRATE
,INTENDED_USE
,DETAIL_TAX_DIST_ID
,REC_NREC_RATE
,RECOVERY_RATE_ID
,RECOVERY_RATE_NAME
,RECOVERY_TYPE_CODE
,RECOVERY_RATE_CODE
,WITHHOLDING_TAX_CODE_ID
,TAX_ALREADY_DISTRIBUTED_FLAG
,SUMMARY_TAX_LINE_ID
,TAXABLE_AMOUNT
,TAXABLE_BASE_AMOUNT
,EXTRA_PO_ERV
,PREPAY_TAX_DIFF_AMOUNT
,TAX_CODE_ID
,VAT_CODE
,AMOUNT_INCLUDES_TAX_FLAG
,TAX_CALCULATED_FLAG
,TAX_RECOVERY_RATE
,TAX_RECOVERY_OVERRIDE_FLAG
,TAX_CODE_OVERRIDE_FLAG
,TOTAL_DIST_AMOUNT
,TOTAL_DIST_BASE_AMOUNT
,PREPAY_TAX_PARENT_ID
,CANCELLED_FLAG
,OLD_DISTRIBUTION_ID
,OLD_DIST_LINE_NUMBER
,AMOUNT_VARIANCE
,BASE_AMOUNT_VARIANCE
,HISTORICAL_FLAG
,RCV_CHARGE_ADDITION_FLAG
,AWT_RELATED_ID
,RELATED_RETAINAGE_DIST_ID
,RETAINED_AMOUNT_REMAINING
,BC_EVENT_ID
,RETAINED_INVOICE_DIST_ID
,FINAL_RELEASE_ROUNDING
,FULLY_PAID_ACCTD_FLAG
,ROOT_DISTRIBUTION_ID
,XINV_PARENT_REVERSAL_ID
,RECURRING_PAYMENT_ID
,RELEASE_INV_DIST_DERIVED_FROM
,PAY_AWT_GROUP_ID
)
VALUES
(p_ap_invoice_dist_all_rec.ACCOUNTING_DATE
,p_ap_invoice_dist_all_rec.ACCRUAL_POSTED_FLAG
,p_ap_invoice_dist_all_rec.ASSETS_ADDITION_FLAG
,p_ap_invoice_dist_all_rec.ASSETS_TRACKING_FLAG
,p_ap_invoice_dist_all_rec.CASH_POSTED_FLAG
,p_ap_invoice_dist_all_rec.DISTRIBUTION_LINE_NUMBER
,p_ap_invoice_dist_all_rec.DIST_CODE_COMBINATION_ID
,p_ap_invoice_dist_all_rec.INVOICE_ID
,p_ap_invoice_dist_all_rec.LAST_UPDATED_BY
,p_ap_invoice_dist_all_rec.LAST_UPDATE_DATE
,p_ap_invoice_dist_all_rec.LINE_TYPE_LOOKUP_CODE
,p_ap_invoice_dist_all_rec.PERIOD_NAME
,p_ap_invoice_dist_all_rec.SET_OF_BOOKS_ID
,p_ap_invoice_dist_all_rec.ACCTS_PAY_CODE_COMBINATION_ID
,p_ap_invoice_dist_all_rec.AMOUNT
,p_ap_invoice_dist_all_rec.BASE_AMOUNT
,p_ap_invoice_dist_all_rec.BASE_INVOICE_PRICE_VARIANCE
,p_ap_invoice_dist_all_rec.BATCH_ID
,p_ap_invoice_dist_all_rec.CREATED_BY
,p_ap_invoice_dist_all_rec.CREATION_DATE
,p_ap_invoice_dist_all_rec.DESCRIPTION
,p_ap_invoice_dist_all_rec.EXCHANGE_RATE_VARIANCE
,p_ap_invoice_dist_all_rec.FINAL_MATCH_FLAG
,p_ap_invoice_dist_all_rec.INCOME_TAX_REGION
,p_ap_invoice_dist_all_rec.INVOICE_PRICE_VARIANCE
,p_ap_invoice_dist_all_rec.LAST_UPDATE_LOGIN
,p_ap_invoice_dist_all_rec.MATCH_STATUS_FLAG
,p_ap_invoice_dist_all_rec.POSTED_FLAG
,p_ap_invoice_dist_all_rec.PO_DISTRIBUTION_ID
,p_ap_invoice_dist_all_rec.PROGRAM_APPLICATION_ID
,p_ap_invoice_dist_all_rec.PROGRAM_ID
,p_ap_invoice_dist_all_rec.PROGRAM_UPDATE_DATE
,p_ap_invoice_dist_all_rec.QUANTITY_INVOICED
,p_ap_invoice_dist_all_rec.RATE_VAR_CODE_COMBINATION_ID
,p_ap_invoice_dist_all_rec.REQUEST_ID
,p_ap_invoice_dist_all_rec.REVERSAL_FLAG
,p_ap_invoice_dist_all_rec.TYPE_1099
,p_ap_invoice_dist_all_rec.UNIT_PRICE
,p_ap_invoice_dist_all_rec.AMOUNT_ENCUMBERED
,p_ap_invoice_dist_all_rec.BASE_AMOUNT_ENCUMBERED
,p_ap_invoice_dist_all_rec.ENCUMBERED_FLAG
,p_ap_invoice_dist_all_rec.EXCHANGE_DATE
,p_ap_invoice_dist_all_rec.EXCHANGE_RATE
,p_ap_invoice_dist_all_rec.EXCHANGE_RATE_TYPE
,p_ap_invoice_dist_all_rec.PRICE_ADJUSTMENT_FLAG
,p_ap_invoice_dist_all_rec.PRICE_VAR_CODE_COMBINATION_ID
,p_ap_invoice_dist_all_rec.QUANTITY_UNENCUMBERED
,p_ap_invoice_dist_all_rec.STAT_AMOUNT
,p_ap_invoice_dist_all_rec.AMOUNT_TO_POST
,p_ap_invoice_dist_all_rec.ATTRIBUTE1
,p_ap_invoice_dist_all_rec.ATTRIBUTE10
,p_ap_invoice_dist_all_rec.ATTRIBUTE11
,p_ap_invoice_dist_all_rec.ATTRIBUTE12
,p_ap_invoice_dist_all_rec.ATTRIBUTE13
,p_ap_invoice_dist_all_rec.ATTRIBUTE14
,p_ap_invoice_dist_all_rec.ATTRIBUTE15
,p_ap_invoice_dist_all_rec.ATTRIBUTE2
,p_ap_invoice_dist_all_rec.ATTRIBUTE3
,p_ap_invoice_dist_all_rec.ATTRIBUTE4
,p_ap_invoice_dist_all_rec.ATTRIBUTE5
,p_ap_invoice_dist_all_rec.ATTRIBUTE6
,p_ap_invoice_dist_all_rec.ATTRIBUTE7
,p_ap_invoice_dist_all_rec.ATTRIBUTE8
,p_ap_invoice_dist_all_rec.ATTRIBUTE9
,p_ap_invoice_dist_all_rec.ATTRIBUTE_CATEGORY
,p_ap_invoice_dist_all_rec.BASE_AMOUNT_TO_POST
,p_ap_invoice_dist_all_rec.CASH_JE_BATCH_ID
,p_ap_invoice_dist_all_rec.EXPENDITURE_ITEM_DATE
,p_ap_invoice_dist_all_rec.EXPENDITURE_ORGANIZATION_ID
,p_ap_invoice_dist_all_rec.EXPENDITURE_TYPE
,p_ap_invoice_dist_all_rec.JE_BATCH_ID
,p_ap_invoice_dist_all_rec.PARENT_INVOICE_ID
,p_ap_invoice_dist_all_rec.PA_ADDITION_FLAG
,p_ap_invoice_dist_all_rec.PA_QUANTITY
,p_ap_invoice_dist_all_rec.POSTED_AMOUNT
,p_ap_invoice_dist_all_rec.POSTED_BASE_AMOUNT
,p_ap_invoice_dist_all_rec.PREPAY_AMOUNT_REMAINING
,p_ap_invoice_dist_all_rec.PROJECT_ACCOUNTING_CONTEXT
,p_ap_invoice_dist_all_rec.PROJECT_ID
,p_ap_invoice_dist_all_rec.TASK_ID
,p_ap_invoice_dist_all_rec.USSGL_TRANSACTION_CODE
,p_ap_invoice_dist_all_rec.USSGL_TRX_CODE_CONTEXT
,p_ap_invoice_dist_all_rec.EARLIEST_SETTLEMENT_DATE
,p_ap_invoice_dist_all_rec.REQ_DISTRIBUTION_ID
,p_ap_invoice_dist_all_rec.QUANTITY_VARIANCE
,p_ap_invoice_dist_all_rec.BASE_QUANTITY_VARIANCE
,p_ap_invoice_dist_all_rec.PACKET_ID
,p_ap_invoice_dist_all_rec.AWT_FLAG
,p_ap_invoice_dist_all_rec.AWT_GROUP_ID
,p_ap_invoice_dist_all_rec.AWT_TAX_RATE_ID
,p_ap_invoice_dist_all_rec.AWT_GROSS_AMOUNT
,p_ap_invoice_dist_all_rec.AWT_INVOICE_ID
,p_ap_invoice_dist_all_rec.AWT_ORIGIN_GROUP_ID
,p_ap_invoice_dist_all_rec.REFERENCE_1
,p_ap_invoice_dist_all_rec.REFERENCE_2
,p_ap_invoice_dist_all_rec.ORG_ID
,p_ap_invoice_dist_all_rec.OTHER_INVOICE_ID
,p_ap_invoice_dist_all_rec.AWT_INVOICE_PAYMENT_ID
,p_ap_invoice_dist_all_rec.GLOBAL_ATTRIBUTE_CATEGORY
,p_ap_invoice_dist_all_rec.GLOBAL_ATTRIBUTE1
,p_ap_invoice_dist_all_rec.GLOBAL_ATTRIBUTE2
,p_ap_invoice_dist_all_rec.GLOBAL_ATTRIBUTE3
,p_ap_invoice_dist_all_rec.GLOBAL_ATTRIBUTE4
,p_ap_invoice_dist_all_rec.GLOBAL_ATTRIBUTE5
,p_ap_invoice_dist_all_rec.GLOBAL_ATTRIBUTE6
,p_ap_invoice_dist_all_rec.GLOBAL_ATTRIBUTE7
,p_ap_invoice_dist_all_rec.GLOBAL_ATTRIBUTE8
,p_ap_invoice_dist_all_rec.GLOBAL_ATTRIBUTE9
,p_ap_invoice_dist_all_rec.GLOBAL_ATTRIBUTE10
,p_ap_invoice_dist_all_rec.GLOBAL_ATTRIBUTE11
,p_ap_invoice_dist_all_rec.GLOBAL_ATTRIBUTE12
,p_ap_invoice_dist_all_rec.GLOBAL_ATTRIBUTE13
,p_ap_invoice_dist_all_rec.GLOBAL_ATTRIBUTE14
,p_ap_invoice_dist_all_rec.GLOBAL_ATTRIBUTE15
,p_ap_invoice_dist_all_rec.GLOBAL_ATTRIBUTE16
,p_ap_invoice_dist_all_rec.GLOBAL_ATTRIBUTE17
,p_ap_invoice_dist_all_rec.GLOBAL_ATTRIBUTE18
,p_ap_invoice_dist_all_rec.GLOBAL_ATTRIBUTE19
,p_ap_invoice_dist_all_rec.GLOBAL_ATTRIBUTE20
,p_ap_invoice_dist_all_rec.LINE_GROUP_NUMBER
,p_ap_invoice_dist_all_rec.RECEIPT_VERIFIED_FLAG
,p_ap_invoice_dist_all_rec.RECEIPT_REQUIRED_FLAG
,p_ap_invoice_dist_all_rec.RECEIPT_MISSING_FLAG
,p_ap_invoice_dist_all_rec.JUSTIFICATION
,p_ap_invoice_dist_all_rec.EXPENSE_GROUP
,p_ap_invoice_dist_all_rec.START_EXPENSE_DATE
,p_ap_invoice_dist_all_rec.END_EXPENSE_DATE
,p_ap_invoice_dist_all_rec.RECEIPT_CURRENCY_CODE
,p_ap_invoice_dist_all_rec.RECEIPT_CONVERSION_RATE
,p_ap_invoice_dist_all_rec.RECEIPT_CURRENCY_AMOUNT
,p_ap_invoice_dist_all_rec.DAILY_AMOUNT
,p_ap_invoice_dist_all_rec.WEB_PARAMETER_ID
,p_ap_invoice_dist_all_rec.ADJUSTMENT_REASON
,p_ap_invoice_dist_all_rec.AWARD_ID
,p_ap_invoice_dist_all_rec.MRC_ACCRUAL_POSTED_FLAG
,p_ap_invoice_dist_all_rec.MRC_CASH_POSTED_FLAG
,p_ap_invoice_dist_all_rec.MRC_DIST_CODE_COMBINATION_ID
,p_ap_invoice_dist_all_rec.MRC_AMOUNT
,p_ap_invoice_dist_all_rec.MRC_BASE_AMOUNT
,p_ap_invoice_dist_all_rec.MRC_BASE_INV_PRICE_VARIANCE
,p_ap_invoice_dist_all_rec.MRC_EXCHANGE_RATE_VARIANCE
,p_ap_invoice_dist_all_rec.MRC_POSTED_FLAG
,p_ap_invoice_dist_all_rec.MRC_PROGRAM_APPLICATION_ID
,p_ap_invoice_dist_all_rec.MRC_PROGRAM_ID
,p_ap_invoice_dist_all_rec.MRC_PROGRAM_UPDATE_DATE
,p_ap_invoice_dist_all_rec.MRC_RATE_VAR_CCID
,p_ap_invoice_dist_all_rec.MRC_REQUEST_ID
,p_ap_invoice_dist_all_rec.MRC_EXCHANGE_DATE
,p_ap_invoice_dist_all_rec.MRC_EXCHANGE_RATE
,p_ap_invoice_dist_all_rec.MRC_EXCHANGE_RATE_TYPE
,p_ap_invoice_dist_all_rec.MRC_AMOUNT_TO_POST
,p_ap_invoice_dist_all_rec.MRC_BASE_AMOUNT_TO_POST
,p_ap_invoice_dist_all_rec.MRC_CASH_JE_BATCH_ID
,p_ap_invoice_dist_all_rec.MRC_JE_BATCH_ID
,p_ap_invoice_dist_all_rec.MRC_POSTED_AMOUNT
,p_ap_invoice_dist_all_rec.MRC_POSTED_BASE_AMOUNT
,p_ap_invoice_dist_all_rec.MRC_RECEIPT_CONVERSION_RATE
,p_ap_invoice_dist_all_rec.CREDIT_CARD_TRX_ID
,p_ap_invoice_dist_all_rec.DIST_MATCH_TYPE
,p_ap_invoice_dist_all_rec.RCV_TRANSACTION_ID
,p_ap_invoice_dist_all_rec.INVOICE_DISTRIBUTION_ID
,p_ap_invoice_dist_all_rec.PARENT_REVERSAL_ID
,p_ap_invoice_dist_all_rec.TAX_RECOVERABLE_FLAG
,p_ap_invoice_dist_all_rec.PA_CC_AR_INVOICE_ID
,p_ap_invoice_dist_all_rec.PA_CC_AR_INVOICE_LINE_NUM
,p_ap_invoice_dist_all_rec.PA_CC_PROCESSED_CODE
,p_ap_invoice_dist_all_rec.MERCHANT_DOCUMENT_NUMBER
,p_ap_invoice_dist_all_rec.MERCHANT_NAME
,p_ap_invoice_dist_all_rec.MERCHANT_REFERENCE
,p_ap_invoice_dist_all_rec.MERCHANT_TAX_REG_NUMBER
,p_ap_invoice_dist_all_rec.MERCHANT_TAXPAYER_ID
,p_ap_invoice_dist_all_rec.COUNTRY_OF_SUPPLY
,p_ap_invoice_dist_all_rec.MATCHED_UOM_LOOKUP_CODE
,p_ap_invoice_dist_all_rec.GMS_BURDENABLE_RAW_COST
,p_ap_invoice_dist_all_rec.ACCOUNTING_EVENT_ID
,p_ap_invoice_dist_all_rec.PREPAY_DISTRIBUTION_ID
,p_ap_invoice_dist_all_rec.UPGRADE_POSTED_AMT
,p_ap_invoice_dist_all_rec.UPGRADE_BASE_POSTED_AMT
,p_ap_invoice_dist_all_rec.INVENTORY_TRANSFER_STATUS
,p_ap_invoice_dist_all_rec.COMPANY_PREPAID_INVOICE_ID
,p_ap_invoice_dist_all_rec.CC_REVERSAL_FLAG
,p_ap_invoice_dist_all_rec.AWT_WITHHELD_AMT
,p_ap_invoice_dist_all_rec.INVOICE_INCLUDES_PREPAY_FLAG
,p_ap_invoice_dist_all_rec.PRICE_CORRECT_INV_ID
,p_ap_invoice_dist_all_rec.PRICE_CORRECT_QTY
,p_ap_invoice_dist_all_rec.PA_CMT_XFACE_FLAG
,p_ap_invoice_dist_all_rec.CANCELLATION_FLAG
,p_ap_invoice_dist_all_rec.INVOICE_LINE_NUMBER
,p_ap_invoice_dist_all_rec.CORRECTED_INVOICE_DIST_ID
,p_ap_invoice_dist_all_rec.ROUNDING_AMT
,p_ap_invoice_dist_all_rec.CHARGE_APPLICABLE_TO_DIST_ID
,p_ap_invoice_dist_all_rec.CORRECTED_QUANTITY
,p_ap_invoice_dist_all_rec.RELATED_ID
,p_ap_invoice_dist_all_rec.ASSET_BOOK_TYPE_CODE
,p_ap_invoice_dist_all_rec.ASSET_CATEGORY_ID
,p_ap_invoice_dist_all_rec.DISTRIBUTION_CLASS
,p_ap_invoice_dist_all_rec.FINAL_PAYMENT_ROUNDING
,p_ap_invoice_dist_all_rec.FINAL_APPLICATION_ROUNDING
,p_ap_invoice_dist_all_rec.AMOUNT_AT_PREPAY_XRATE
,p_ap_invoice_dist_all_rec.CASH_BASIS_FINAL_APP_ROUNDING
,p_ap_invoice_dist_all_rec.AMOUNT_AT_PREPAY_PAY_XRATE
,p_ap_invoice_dist_all_rec.INTENDED_USE
,p_ap_invoice_dist_all_rec.DETAIL_TAX_DIST_ID
,p_ap_invoice_dist_all_rec.REC_NREC_RATE
,p_ap_invoice_dist_all_rec.RECOVERY_RATE_ID
,p_ap_invoice_dist_all_rec.RECOVERY_RATE_NAME
,p_ap_invoice_dist_all_rec.RECOVERY_TYPE_CODE
,p_ap_invoice_dist_all_rec.RECOVERY_RATE_CODE
,p_ap_invoice_dist_all_rec.WITHHOLDING_TAX_CODE_ID
,p_ap_invoice_dist_all_rec.TAX_ALREADY_DISTRIBUTED_FLAG
,p_ap_invoice_dist_all_rec.SUMMARY_TAX_LINE_ID
,p_ap_invoice_dist_all_rec.TAXABLE_AMOUNT
,p_ap_invoice_dist_all_rec.TAXABLE_BASE_AMOUNT
,p_ap_invoice_dist_all_rec.EXTRA_PO_ERV
,p_ap_invoice_dist_all_rec.PREPAY_TAX_DIFF_AMOUNT
,p_ap_invoice_dist_all_rec.TAX_CODE_ID
,p_ap_invoice_dist_all_rec.VAT_CODE
,p_ap_invoice_dist_all_rec.AMOUNT_INCLUDES_TAX_FLAG
,p_ap_invoice_dist_all_rec.TAX_CALCULATED_FLAG
,p_ap_invoice_dist_all_rec.TAX_RECOVERY_RATE
,p_ap_invoice_dist_all_rec.TAX_RECOVERY_OVERRIDE_FLAG
,p_ap_invoice_dist_all_rec.TAX_CODE_OVERRIDE_FLAG
,p_ap_invoice_dist_all_rec.TOTAL_DIST_AMOUNT
,p_ap_invoice_dist_all_rec.TOTAL_DIST_BASE_AMOUNT
,p_ap_invoice_dist_all_rec.PREPAY_TAX_PARENT_ID
,p_ap_invoice_dist_all_rec.CANCELLED_FLAG
,p_ap_invoice_dist_all_rec.OLD_DISTRIBUTION_ID
,p_ap_invoice_dist_all_rec.OLD_DIST_LINE_NUMBER
,p_ap_invoice_dist_all_rec.AMOUNT_VARIANCE
,p_ap_invoice_dist_all_rec.BASE_AMOUNT_VARIANCE
,p_ap_invoice_dist_all_rec.HISTORICAL_FLAG
,p_ap_invoice_dist_all_rec.RCV_CHARGE_ADDITION_FLAG
,p_ap_invoice_dist_all_rec.AWT_RELATED_ID
,p_ap_invoice_dist_all_rec.RELATED_RETAINAGE_DIST_ID
,p_ap_invoice_dist_all_rec.RETAINED_AMOUNT_REMAINING
,p_ap_invoice_dist_all_rec.BC_EVENT_ID
,p_ap_invoice_dist_all_rec.RETAINED_INVOICE_DIST_ID
,p_ap_invoice_dist_all_rec.FINAL_RELEASE_ROUNDING
,p_ap_invoice_dist_all_rec.FULLY_PAID_ACCTD_FLAG
,p_ap_invoice_dist_all_rec.ROOT_DISTRIBUTION_ID
,p_ap_invoice_dist_all_rec.XINV_PARENT_REVERSAL_ID
,p_ap_invoice_dist_all_rec.RECURRING_PAYMENT_ID
,p_ap_invoice_dist_all_rec.RELEASE_INV_DIST_DERIVED_FROM
,p_ap_invoice_dist_all_rec.PAY_AWT_GROUP_ID
);
END Insert_ap_inv_dst_line;
SELECT
invoice_id
, line_number
, line_type_lookup_code
, description
, org_id
, assets_tracking_flag
, match_type
, accounting_date
, period_name
, deferred_acctg_flag
, def_acctg_start_date
, def_acctg_end_date
, def_acctg_number_of_periods
, def_acctg_period_type
, set_of_books_id
, amount
, wfapproval_status
, creation_date
, created_by
, last_updated_by
, last_update_date
, last_update_login
, project_id
, task_id
, expenditure_type
, expenditure_item_date
, expenditure_organization_id
FROM
ap_invoice_lines_all
WHERE invoice_id = ln_invoice_id
AND line_type_lookup_code = GV_CONSTANT_ITEM
AND match_type = GV_NOT_MATCH_TYPE
AND line_number = NVL (ln_line_number, line_number);
SELECT
MAX(jcdt.doc_tax_id) doc_tax_id
, jcdt.tax_line_no tax_line_no
, jcdt.tax_id tax_id
, MAX(jcdt.tax_type) tax_type
, MAX(jcdt.currency_code) currency_code
, MAX(jcdt.tax_rate) tax_rate
, MAX(jcdt.qty_rate) qty_rate
, MAX(jcdt.uom) uom
, SUM(jcdt.tax_amt) tax_amt
, SUM(jcdt.func_tax_amt) func_tax_amt
, MAX(jcdt.modvat_flag) modvat_flag
, MAX(jcdt.tax_category_id) tax_category_id
, MAX(jcdt.source_doc_type) source_doc_type
, MAX(jcdt.source_doc_id) source_doc_id
, MAX(jcdt.source_doc_line_id) source_doc_line_id
, MAX(jcdt.source_table_name) source_table_name
, MAX(jcdt.tax_modified_by) tax_modified_by
, MAX(jcdt.adhoc_flag) adhoc_flag
, MAX(jcdt.precedence_1) precedence_1
, MAX(jcdt.precedence_2) precedence_2
, MAX(jcdt.precedence_3) precedence_3
, MAX(jcdt.precedence_4) precedence_4
, MAX(jcdt.precedence_5) precedence_5
, MAX(jcdt.precedence_6) precedence_6
, MAX(jcdt.precedence_7) precedence_7
, MAX(jcdt.precedence_8) precedence_8
, MAX(jcdt.precedence_9) precedence_9
, MAX(jcdt.precedence_10) precedence_10
, MAX(jcdt.creation_date) creation_date
, MAX(jcdt.created_by) created_by
, MAX(jcdt.last_update_date) last_update_date
, MAX(jcdt.last_updated_by) last_updated_by
, MAX(jcdt.last_update_login) last_update_login
, MAX(jcdt.object_version_number) object_version_number
, MAX(jcdt.vendor_id) vendor_id
, MAX(jcdt.source_doc_parent_line_no) source_doc_parent_line_no
, MAX(jcta.inclusive_tax_flag) inc_tax_flag
FROM
jai_cmn_document_taxes jcdt
, jai_cmn_taxes_all jcta --Added by Eric for Inclusive Tax
WHERE jcdt.source_doc_id = pn_invoice_id
AND jcdt.source_doc_parent_line_no = pn_parent_line_number
AND jcdt.tax_id = jcta.tax_id --Added by Eric for Inclusive Tax
AND jcdt.source_doc_type = jai_constants.g_ap_standalone_invoice
AND NVL(jcta.reverse_charge_flag,'N') = 'Y' --Added by Chong for reverse charge service tax ER 2012/07/05
--Added by Chong for reverse charge service tax ER bug#14385555 2012/08/06 Start
---------------------------------------------------------------------------------
AND NOT EXISTS(
SELECT 1
FROM ap_invoices_all aia_in
WHERE jcdt.source_table_name = 'JAI_AP_INVOICE_LINES'
AND jcdt.source_doc_id = aia_in.invoice_id
AND aia_in.invoice_type_lookup_code in ('CREDIT' ,'DEBIT')
)
---------------------------------------------------------------------------------
--Added by Chong for reverse charge service tax ER bug#14385555 2012/08/06 End
GROUP by jcdt.tax_id,jcdt.tax_line_no --Added by Chong for reverse charge service tax ER 2012/07/05
ORDER BY MAX(jcdt.doc_tax_id);
SELECT
jcdt.doc_tax_id
, jcdt.tax_line_no
, jcdt.tax_id
, jcdt.tax_type
, jcdt.currency_code
, jcdt.tax_rate
, jcdt.qty_rate
, jcdt.uom
, jcdt.tax_amt
, jcdt.func_tax_amt
, jcdt.modvat_flag
, jcdt.tax_category_id
, jcdt.source_doc_type
, jcdt.source_doc_id
, jcdt.source_doc_line_id
, jcdt.source_table_name
, jcdt.tax_modified_by
, jcdt.adhoc_flag
, jcdt.precedence_1
, jcdt.precedence_2
, jcdt.precedence_3
, jcdt.precedence_4
, jcdt.precedence_5
, jcdt.precedence_6
, jcdt.precedence_7
, jcdt.precedence_8
, jcdt.precedence_9
, jcdt.precedence_10
, jcdt.creation_date
, jcdt.created_by
, jcdt.last_update_date
, jcdt.last_updated_by
, jcdt.last_update_login
, jcdt.object_version_number
, jcdt.vendor_id
, jcdt.source_doc_parent_line_no
, jcta.inclusive_tax_flag inc_tax_flag --Added by Eric for Inclusive Tax
FROM
jai_cmn_document_taxes jcdt
, jai_cmn_taxes_all jcta --Added by Eric for Inclusive Tax
WHERE jcdt.source_doc_id = pn_invoice_id
AND jcdt.source_doc_parent_line_no = pn_parent_line_number
AND jcdt.tax_id = jcta.tax_id --Added by Eric for Inclusive Tax
AND jcdt.source_doc_type = jai_constants.g_ap_standalone_invoice
AND NVL(jcta.reverse_charge_flag,'N') = 'Y' --Added by Chong for reverse charge service tax ER 20120705
AND NVL(jcdt.modvat_flag, 'N') = 'Y' --Added by Chong for reverse charge service tax ER 20120705
--Added by Chong for reverse charge service tax ER bug#14385555 2012/08/06 Start
---------------------------------------------------------------------------------
AND NOT EXISTS(
SELECT 1
FROM ap_invoices_all aia_in
WHERE jcdt.source_table_name = 'JAI_AP_INVOICE_LINES'
AND jcdt.source_doc_id = aia_in.invoice_id
AND aia_in.invoice_type_lookup_code in ('CREDIT' ,'DEBIT')
)
---------------------------------------------------------------------------------
--Added by Chong for reverse charge service tax ER bug#14385555 2012/08/06 End
ORDER BY jcdt.doc_tax_id FOR UPDATE OF source_doc_line_id; --Add for update by Xiao for Accounting Issue, port changes for pot bug#12533434
SELECT
jcdt.doc_tax_id
, jcdt.tax_line_no
, jcdt.tax_id
, jcdt.tax_type
, jcdt.currency_code
, jcdt.tax_rate
, jcdt.qty_rate
, jcdt.uom
, jcdt.tax_amt
, jcdt.func_tax_amt
, jcdt.modvat_flag
, jcdt.tax_category_id
, jcdt.source_doc_type
, jcdt.source_doc_id
, jcdt.source_doc_line_id
, jcdt.source_table_name
, jcdt.tax_modified_by
, jcdt.adhoc_flag
, jcdt.precedence_1
, jcdt.precedence_2
, jcdt.precedence_3
, jcdt.precedence_4
, jcdt.precedence_5
, jcdt.precedence_6
, jcdt.precedence_7
, jcdt.precedence_8
, jcdt.precedence_9
, jcdt.precedence_10
, jcdt.creation_date
, jcdt.created_by
, jcdt.last_update_date
, jcdt.last_updated_by
, jcdt.last_update_login
, jcdt.object_version_number
, jcdt.vendor_id
, jcdt.source_doc_parent_line_no
FROM
jai_cmn_document_taxes jcdt
WHERE jcdt.source_doc_id = pn_invoice_id
AND jcdt.source_doc_line_id = pn_line_number
AND jcdt.source_doc_parent_line_no = pn_line_number
AND jcdt.source_doc_type = jai_constants.g_ap_standalone_invoice
ORDER BY jcdt.tax_line_no FOR UPDATE;
SELECT
tax_name
, tax_account_id
, mod_cr_percentage
, adhoc_flag
, NVL (tax_rate, -1) tax_rate
, tax_type
, NVL(rounding_factor,0) rounding_factor
FROM
jai_cmn_taxes_all
WHERE tax_id = pn_tax_id;
SELECT
accounting_date
, accrual_posted_flag
, assets_addition_flag
, assets_tracking_flag
, cash_posted_flag
, distribution_line_number
, dist_code_combination_id
, invoice_id
, last_updated_by
, last_update_date
, line_type_lookup_code
, period_name
, set_of_books_id
, amount
, base_amount
, batch_id
, created_by
, creation_date
, description
, exchange_rate
, exchange_rate_variance
, last_update_login
, match_status_flag
, posted_flag
, rate_var_code_combination_id
, reversal_flag
, program_application_id
, program_id
, program_update_date
, accts_pay_code_combination_id
, invoice_distribution_id
, quantity_invoiced
, po_distribution_id
, rcv_transaction_id
, price_var_code_combination_id
, invoice_price_variance
, base_invoice_price_variance
, matched_uom_lookup_code
, invoice_line_number
, org_id
, charge_applicable_to_dist_id
, project_id
, task_id
, expenditure_type
, expenditure_item_date
, expenditure_organization_id
, project_accounting_context
, pa_addition_flag
, distribution_class
, ASSET_BOOK_TYPE_CODE
, ASSET_CATEGORY_ID
,accounting_event_id --added for bug#10044104
FROM
ap_invoice_distributions_all
WHERE invoice_id = ln_invoice_id
AND invoice_line_number = pn_line_number
AND distribution_line_number = pn_distribution_line_number; /*1; Bug 13617527 */
SELECT service_type_code,organization_id, location_id
FROM jai_ap_invoice_lines
WHERE invoice_id = p_invoice_id
AND invoice_line_number = p_invoice_line_number;
select count(*)
from jai_ap_invoice_lines
where invoice_id=p_invoice_id
and invoice_line_number=p_invoice_line_number;
select count(*)
from ap_invoice_lines_all
where invoice_id=p_invoice_id
and line_number=p_invoice_line_number;
select count(*)
from ap_invoice_distributions_all
where invoice_id = p_invoice_id
and invoice_line_number = p_invoice_line_number
and distribution_line_number = p_distribution_line_number;
select min(distribution_line_number) into ln_distribution_line_number
from ap_invoice_distributions_all where invoice_id = pn_invoice_id
and invoice_line_number = ap_invoice_lines_rec.line_number;
SELECT jai_ap_invoice_lines_s.NEXTVAL
INTO ln_jai_inv_line_id FROM DUAL;
jai_ap_invoice_lines_rec.last_update_date := SYSDATE;
jai_ap_invoice_lines_rec.last_update_login := ln_login_id;
jai_ap_invoice_lines_rec.last_updated_by := ln_user_id;
Insert_jai_inv_line(jai_ap_invoice_lines_rec);
, 'Table jai_ap_invoice_lines inserted '
);
ap_invoice_lines_all_rec.last_updated_by := ln_user_id;
ap_invoice_lines_all_rec.last_update_date := SYSDATE;
ap_invoice_lines_all_rec.last_update_login := ln_login_id;
Insert_ap_inv_line(ap_invoice_lines_all_rec);
, 'Table ap_invoice_lines_all inserted '
);
SELECT
ap_invoice_distributions_s.NEXTVAL
INTO
ln_inv_dist_id
FROM DUAL;
select invoice_distribution_id into lv_invoice_distribution_id
FROM ap_invoice_distributions_all
WHERE invoice_id = pn_invoice_id
AND invoice_line_number = pn_line_number
AND distribution_line_number =(select min(distribution_line_number) from ap_invoice_distributions_all
where invoice_id = pn_invoice_id AND invoice_line_number = pn_line_number) ;
SELECT count(*)
INTO l_null_event_id
FROM ap_invoice_distributions aid
WHERE aid.invoice_id = ln_invoice_id
AND aid.accounting_event_id is NULL;
, 'ln_inv_dist_id:'||ln_inv_dist_id||'ln_dist_acct_ccid:'||ln_dist_acct_ccid||'Table ap_invoice_distributions_all inserted '
);
ap_invoice_dist_all_rec.last_updated_by := ln_user_id;
ap_invoice_dist_all_rec.last_update_date := SYSDATE;
ap_invoice_dist_all_rec.last_update_login := ln_login_id;
ap_invoice_dist_all_rec.program_update_date := ap_invoice_dist_rec.program_update_date;
Insert_ap_inv_dst_line(ap_invoice_dist_all_rec);
, 'ln_dist_acct_ccid:'||ln_dist_acct_ccid||',Table ap_distribution_lines_all inserted '
);
'@@Before insert reverse charege service tax liability lines.');
select min(distribution_line_number) into ln_distribution_line_number
from ap_invoice_distributions_all where invoice_id = pn_invoice_id
and invoice_line_number = ap_invoice_lines_rec.line_number;
SELECT
jai_ap_invoice_lines_s.NEXTVAL
INTO
ln_jai_inv_line_id
FROM DUAL;
jai_ap_invoice_lines_rec.last_update_date := SYSDATE;
jai_ap_invoice_lines_rec.last_update_login := ln_login_id;
jai_ap_invoice_lines_rec.last_updated_by := ln_user_id;
Insert_jai_inv_line(jai_ap_invoice_lines_rec);
, 'Table jai_ap_invoice_lines for liability line inserted '
);
ap_invoice_lines_all_rec.last_updated_by := ln_user_id;
ap_invoice_lines_all_rec.last_update_date := SYSDATE;
ap_invoice_lines_all_rec.last_update_login := ln_login_id;
Insert_ap_inv_line(ap_invoice_lines_all_rec);
, 'Table ap_invoice_lines_all for liability line inserted '
);
SELECT
ap_invoice_distributions_s.NEXTVAL
INTO
ln_inv_dist_id
FROM DUAL;
select invoice_distribution_id into lv_invoice_distribution_id
FROM ap_invoice_distributions_all
WHERE invoice_id = pn_invoice_id
AND invoice_line_number = pn_line_number
AND distribution_line_number =(select min(distribution_line_number) from ap_invoice_distributions_all
where invoice_id = pn_invoice_id AND invoice_line_number = pn_line_number) ;
SELECT count(*)
INTO l_null_event_id
FROM ap_invoice_distributions aid
WHERE aid.invoice_id = ln_invoice_id
AND aid.accounting_event_id is NULL;
ap_invoice_dist_all_rec.last_updated_by := ln_user_id;
ap_invoice_dist_all_rec.last_update_date := SYSDATE;
ap_invoice_dist_all_rec.last_update_login := ln_login_id;
ap_invoice_dist_all_rec.program_update_date := ap_invoice_dist_rec.program_update_date;
Insert_ap_inv_dst_line(ap_invoice_dist_all_rec);
, 'Table ap_distribution_lines_all for liability line inserted '
);
SELECT vendor_id, vendor_site_id, org_id, cancelled_date, invoice_num, set_of_books_id, -- added for bug#3354932
legal_entity_id /* rallamse bug#4448789 */
FROM ap_invoices_all
WHERE invoice_id = v_invoice_id;
Select nvl(discarded_flag,'N') from ap_invoice_lines_all
where invoice_id = ln_invoice_id
and line_number = pn_invoice_line_number
and po_distribution_id = po_dist_id;
select invoice_id, po_distribution_id, invoice_distribution_id, rcv_transaction_id,invoice_line_number,
parent_invoice_distribution_id
from jai_ap_match_inv_taxes
where line_type_lookup_code = 'MISCELLANEOUS'
and invoice_id = p_invoice_id
and parent_invoice_line_number = pn_invoice_line_number
and po_distribution_id = po_dist_id;
SELECT currency_code
FROM gl_sets_of_books
WHERE set_of_books_id = p_sob;
SELECT pay_on_code, pay_on_receipt_summary_code
FROM po_vendor_sites_all
WHERE vendor_id = ven_id
AND vendor_site_id = ven_site_id
AND NVL(org_id, -1) = NVL(v_org_id, -1);
select ship_to_organization_id
,ship_to_location_id
from po_line_locations_all plla
where plla.line_location_id = cpn_line_location_id;
SELECT pod.po_header_id,
pod.po_line_id,
pod.line_location_id,
pod.set_of_books_id,
pod.org_id,
poh.rate,
poh.rate_type,
pod.rate_date,
poh.currency_code,
api.last_update_login,
apd.dist_code_combination_id,
api.creation_date,
api.created_by,
api.last_update_date,
api.last_updated_by,
api.invoice_date
FROM ap_invoices_all api,
ap_invoice_distributions_all apd,
po_distributions_all pod,
po_headers_all poh
WHERE apd.invoice_id = api.invoice_id
AND pod.po_header_id = poh.po_header_id
AND apd.po_distribution_id = pod.po_distribution_id
AND apd.invoice_line_number = line_no
AND api.invoice_id = inv_id
AND apd.distribution_line_number = cpn_min_dist_line_no;
SELECT min(distribution_line_number)
FROM ap_invoice_distributions_all apid
WHERE apid.invoice_id = cpn_invoice_id
AND apid.invoice_line_number = cpn_invoice_line_number;
SELECT line_location_id, po_line_id
FROM po_distributions_all
WHERE po_distribution_id = po_dist_id;
SELECT po_line_id, po_line_location_id
FROM ap_invoice_lines_all
WHERE invoice_id = inv_id
AND line_number = pn_invoice_line_number;
SELECT a.accounting_date,
a.accrual_posted_flag,
a.assets_addition_flag,
a.assets_tracking_flag,
a.cash_posted_flag,
a.dist_code_combination_id,
a.last_updated_by,
a.last_update_date,
a.line_type_lookup_code,
a.period_name,
a.set_of_books_id,
a.amount,
a.base_amount,
a.batch_id,
a.created_by,
a.creation_date,
a.description,
a.accts_pay_code_combination_id,
a.exchange_rate_variance,
a.last_update_login,
a.match_status_flag,
a.posted_flag,
a.rate_var_code_combination_id,
a.reversal_flag,
a.vat_code,
a.exchange_date,
NVL(a.exchange_rate,1) exchange_rate,
a.exchange_rate_type,
a.price_adjustment_flag,
a.program_application_id,
a.program_id,
a.program_update_date,
a.global_attribute1,
a.global_attribute2,
a.global_attribute3,
a.po_distribution_id,--rchandan for bug#4333488
a.project_id,
a.task_id,
a.expenditure_type,
a.expenditure_item_date,
a.expenditure_organization_id,
a.quantity_invoiced,
Nvl(a.quantity_invoiced,1)/Nvl(b.quantity_invoiced,1) qty_apportion_factor,
a.unit_price,
price_var_code_combination_id,
invoice_distribution_id,
matched_uom_lookup_code,
invoice_price_variance,
a.distribution_line_number,
a.org_id -- Test for Bug 4863208
/* 5763527 */
,project_accounting_context
,pa_addition_flag
/* End 5763527 */
,a.dist_match_type --Added by nprashar for bug # 12832424
FROM ap_invoice_distributions_all a,
ap_invoice_lines_all b
WHERE a.invoice_id = cp_invoice_id
AND a.invoice_line_number = cp_line_num
AND b.invoice_id = cp_invoice_id
AND b.line_number = cp_line_num
AND a.invoice_id = b.invoice_id
AND a.invoice_line_number = b.line_number
ORDER BY a.distribution_line_number;
SELECT
jpt.tax_id
, jpt.tax_amount
, jpt.currency
, jpt.tax_target_amount
, nvl(jpt.modvat_flag,'Y') modvat_flag
, jpt.tax_type
, jpt.tax_line_no -- added by kunkumar for bug 5593895
, NVL(jcta.inclusive_tax_flag,'N') inc_tax_flag --Added by Eric for Inclusive Tax
,nvl(jcta.vat_flag,'N') VAT_FLAG
,nvl(jcta.adhoc_flag,'N') adhoc_flag
FROM
JAI_PO_TAXES jpt
, JAI_CMN_TAXES_ALL jcta --Added by Eric for Inclusive Tax
-- WHERE line_focus_id = focus_id
WHERE line_location_id = p_line_location_id -- 3096578
AND NVL(UPPER(jpt.tax_type), 'A')
NOT IN( 'TDS'
, 'CVD'
, jai_constants.tax_type_add_cvd -- Date 31/10/2006 Bug 5228046 added by SACSETHI
, 'CUSTOMS'
, jai_constants.tax_type_cvd_edu_cess
, jai_constants.tax_type_customs_edu_cess
, jai_constants.tax_type_sh_cvd_edu_cess
, jai_constants.tax_type_sh_customs_edu_cess --Added higher education cess by csahoo for bug#5989740
)
AND jcta.tax_id = jpt.tax_id
AND NVL(jpt.vendor_id, -1) = vend_id --Modified by kunkumar for bug 5593895
AND p_source <> 'PPA'
--Added by Chong for reverse charge service tax on 2012/07/05 start
---------------------------------------------------------------------
AND NVL(jcta.reverse_charge_flag,'N') = 'Y'
---------------------------------------------------------------------
--Added by Chong for reverse charge service tax on 2012/07/05 end
-- Added by Jason Liu for bug#6918386
---------------------------------------------------------------------
UNION
SELECT
jrl.tax_id
, (jrl.modified_tax_amount - jrl.original_tax_amount) tax_amount
, jrl.currency_code currency
, (jrl.modified_tax_amount - jrl.original_tax_amount) tax_target_amount
, jrl.recoverable_flag modvat_flag
, jrl.tax_type tax_type
, jrl.tax_line_no tax_line_no
, NVL(jcta.inclusive_tax_flag,'N') inc_tax_flag
,nvl(jcta.vat_flag,'N') VAT_FLAG
,nvl(jcta.adhoc_flag,'N') adhoc_flag
FROM
jai_retro_tax_changes jrl
, jai_retro_line_changes jrlc
, jai_cmn_taxes_all jcta
WHERE jrlc.line_location_id = p_line_location_id
AND jrlc.line_change_id = jrl.line_change_id
AND jrlc.doc_version_number = (SELECT max(doc_version_number)
FROM jai_retro_line_changes
WHERE doc_type IN ('STANDARD PO', 'RELEASE')
AND doc_line_id = jrlc.doc_line_id
)
AND jrlc.doc_type IN ('STANDARD PO', 'RELEASE')
AND jrl.tax_id = jcta.tax_id
AND NVL(jrlc.vendor_id, -1) = vend_id
AND p_source = 'PPA'
AND NVL(upper(jrl.tax_type),'TDS') NOT IN ( jai_constants.tax_type_tds
, jai_constants.tax_type_cvd
, jai_constants.tax_type_add_cvd
, jai_constants.tax_type_customs
, jai_constants.tax_type_sh_customs_edu_cess
, jai_constants.tax_type_customs_edu_cess
, jai_constants.tax_type_sh_cvd_edu_cess
, jai_constants.tax_type_cvd_edu_cess
)
AND NVL(jrl.third_party_flag, 'N') = 'N' -- Added by Jason Liu for bug#6936416
---------------------------------------------------------------------
--Added by Chong for reverse charge service tax on 2012/07/05 start
---------------------------------------------------------------------
AND NVL(jcta.reverse_charge_flag,'N') = 'Y'
---------------------------------------------------------------------
--Added by Chong for reverse charge service tax on 2012/07/05 end
ORDER BY tax_line_no; -- added bug#3038566
SELECT tax_name,
tax_account_id,
mod_cr_percentage, -- bug 3051828
adhoc_flag, -- added by aparajita on 23/01/2003 for bug # 2694011
nvl(tax_rate,-1) tax_rate, --Modified by kunkumar for bug 5593895
tax_type,
NVL(rounding_factor,0) rounding_factor /* added by vumaasha for bug 6761425 */
FROM JAI_CMN_TAXES_ALL
WHERE tax_id = t_id;
SELECT batch_id,source
FROM ap_invoices_all
WHERE invoice_id = inv_id;
SELECT org_id, vendor_id, NVL(exchange_rate, 1) exchange_rate, invoice_currency_code
FROM ap_invoices_all
WHERE invoice_id = inv_id;
SELECT accts_pay_code_combination_id
FROM ap_system_parameters_all
WHERE NVL(org_id, -1) = NVL(orgn_id, -1);--uncommented and modified by kunkumar for bug 5593895
CURSOR for_dist_insertion(cpn_invoice_id NUMBER, cpn_inv_line_num NUMBER,cpn_min_dist_line_num NUMBER) IS /* Picks up dtls from std apps inserted line */
SELECT a.accounting_date,a.accrual_posted_flag,
a.assets_addition_flag,a.assets_tracking_flag,
a.cash_posted_flag, a.dist_code_combination_id,
a.last_updated_by,a.last_update_date,
a.line_type_lookup_code, a.period_name,
a.set_of_books_id,a.amount,a.base_amount,
a.batch_id,a.created_by,a.creation_date,
a.description,a.accts_pay_code_combination_id,
a.exchange_rate_variance,a.last_update_login,
a.match_status_flag,a.posted_flag, a.rate_var_code_combination_id,
a.reversal_flag,a.vat_code,a.exchange_date,
NVL(a.exchange_rate,1) exchange_rate,
a.exchange_rate_type,a.price_adjustment_flag,
a.program_application_id,a.program_id,
a.program_update_date,a.global_attribute1,
a.global_attribute2,
a.global_attribute3, a.po_distribution_id,--rchandan for bug#4333488
a.project_id,a.task_id,a.expenditure_type,a.expenditure_item_date,
a.expenditure_organization_id, quantity_invoiced,
a.unit_price, price_var_code_combination_id,
invoice_distribution_id, matched_uom_lookup_code, invoice_price_variance,
org_id -- Test for Bug 4863208
/* 5763527 */
,project_accounting_context
,pa_addition_flag
/* End 5763527 */
, dist_match_type --Added by nprashar for bug # 12832424
FROM ap_invoice_distributions_all a
WHERE invoice_id = cpn_invoice_id
AND invoice_line_number = cpn_inv_line_num
AND distribution_line_number = cpn_min_dist_line_num;
SELECT
jrl.tax_amount tax_amount
, jrl.tax_id
, jrl.currency
, jrl.tax_type tax_type
, jrl.modvat_flag
, jrl.tax_line_no tax_line_no --modified by kunkumar for bug 5593895
, NVL(jcta.inclusive_tax_flag,'N') inc_tax_flag --Added by Eric for Inclusive Tax
,jcta.vat_flag vat_flag
,nvl(jcta.adhoc_flag,'N') adhoc_flag --12351311
FROM
JAI_RCV_LINE_TAXES jrl
, RCV_SHIPMENT_LINES RSL
, RCV_TRANSACTIONS RT
, jai_cmn_taxes_all jcta
WHERE jrl.shipment_line_id = rsl.shipment_line_id
AND rt.shipment_line_id = rsl.shipment_line_id
AND rt.transaction_id = tran_id
AND jrl.vendor_id = ven_id
AND jcta.tax_id = jrl.tax_id --Added by Eric for Inclusive Tax
AND NVL(upper(jrl.tax_type),'TDS')
NOT IN ( 'TDS'
, 'CVD'
, jai_constants.tax_type_add_cvd -- Date 31/10/2006 Bug 5228046 added by SACSETHI
, 'CUSTOMS'
, JAI_CONSTANTS.TAX_TYPE_SH_CUSTOMS_EDU_CESS
, jai_constants.tax_type_customs_edu_cess /* added by ssawant for bug 5989740 */
, JAI_CONSTANTS.TAX_TYPE_SH_CVD_EDU_CESS
, jai_constants.tax_type_cvd_edu_cess /* added by ssawant for bug 5989740 */
)
--Added by Chong for reverse charge service tax on 2012/07/05 start
---------------------------------------------------------------------
AND NVL(jcta.reverse_charge_flag,'N') = 'Y'
---------------------------------------------------------------------
--Added by Chong for reverse charge service tax on 2012/07/05 end
order by tax_line_no; -- added
SELECT
jrl.tax_amount tax_amount
, jrl.tax_id
, jrl.currency
, jrl.tax_type tax_type
, jrl.modvat_flag
, jrl.tax_line_no tax_line_no --Added by kunkumar for bug 5593895
, NVL(jcta.inclusive_tax_flag,'N') inc_tax_flag --Added by Eric for Inclusive Tax
,jcta.vat_flag vat_flag
,nvl(jcta.adhoc_flag,'N') ADHOC_FLAG --12351311
FROM
jai_rcv_line_taxes jrl
, rcv_shipment_lines rsl
, rcv_transactions rt
, jai_cmn_taxes_all jcta --Added by Eric for Inclusive Tax
WHERE jrl.shipment_line_id = rsl.shipment_line_id
AND rt.shipment_line_id = rsl.shipment_line_id
AND rt.transaction_id = tran_id
AND jrl.vendor_id = ven_id
AND jcta.tax_id = jrl.tax_id --Added by Eric for Inclusive Tax
AND NVL(upper(jrl.tax_type),'TDS') NOT IN
('TDS',
'CVD',
jai_constants.tax_type_add_cvd , -- Date 31/10/2006 Bug 5228046 added by SACSETHI
'CUSTOMS',
JAI_CONSTANTS.TAX_TYPE_SH_CUSTOMS_EDU_CESS,jai_constants.tax_type_customs_edu_cess, /* added by ssawant for bug 5989740 */
JAI_CONSTANTS.TAX_TYPE_SH_CVD_EDU_CESS,jai_constants.tax_type_cvd_edu_cess /* added by ssawant for bug 5989740 */
)
AND p_source <> 'PPA'
--Added by Chong for reverse charge service tax on 2012/07/05 start
---------------------------------------------------------------------
AND NVL(jcta.reverse_charge_flag,'N') = 'Y'
---------------------------------------------------------------------
--Added by Chong for reverse charge service tax on 2012/07/05 end
UNION
SELECT
(jrl.modified_tax_amount - jrl.original_tax_amount) tax_amount
, jrl.tax_id
, jrl.currency_code currency
, jrl.tax_type tax_type
, jrl.recoverable_flag modvat_flag
, jrl.tax_line_no tax_line_no
, NVL(jcta.inclusive_tax_flag,'N') inc_tax_flag
,jcta.vat_flag vat_flag
,nvl(jcta.adhoc_flag,'N') ADHOC_FLAG --12351311
FROM
jai_retro_tax_changes jrl
, rcv_shipment_lines rsl
, rcv_transactions rt
, jai_retro_line_changes jrlc
, jai_cmn_taxes_all jcta
WHERE jrlc.doc_line_id = rsl.shipment_line_id
AND jrlc.line_change_id = jrl.line_change_id
AND jrlc.doc_version_number = (SELECT max(doc_version_number)
FROM jai_retro_line_changes
WHERE doc_type = 'RECEIPT'
AND doc_line_id = jrlc.doc_line_id
)
AND jrlc.doc_type = 'RECEIPT'
AND jrl.tax_id = jcta.tax_id
AND rt.shipment_line_id = rsl.shipment_line_id
AND rt.transaction_id = tran_id
AND jrlc.vendor_id = ven_id
AND p_source = 'PPA'
AND NVL(upper(jrl.tax_type),'TDS') NOT IN ( jai_constants.tax_type_tds
, jai_constants.tax_type_cvd
, jai_constants.tax_type_add_cvd
, jai_constants.tax_type_customs
, jai_constants.tax_type_sh_customs_edu_cess
, jai_constants.tax_type_customs_edu_cess
, jai_constants.tax_type_sh_cvd_edu_cess
, jai_constants.tax_type_cvd_edu_cess
)
AND NVL(jrl.third_party_flag, 'N') = 'N' -- Added by Jason Liu for bug#6936416
--Added by Chong for reverse charge service tax on 2012/07/05 start
---------------------------------------------------------------------
AND NVL(jcta.reverse_charge_flag,'N') = 'Y'
---------------------------------------------------------------------
--Added by Chong for reverse charge service tax on 2012/07/05 end
-- GROUP BY jrl.tax_id,jrl.currency,jrl.tax_type, jrl.modvat_flag; commented by bug#3038566
select ap_invoice_distributions_s.nextval
from dual;
select precision
from fnd_currencies
where currency_code = cp_currency_code;
select NVL(rounding_factor,2) -- for bug 16226028
from jai_cmn_taxes_all
where tax_id = cp_tax_id ;
select source
from ap_invoices_all
where invoice_id = p_inv_id;
v_update_payment_schedule boolean; -- bug#3218978
for_dist_insertion_rec for_dist_insertion%ROWTYPE;
ln_lines_to_insert number;
select attribute2 excise_costing_flag
from jai_rcv_transactions jrcvt
where jrcvt.parent_transaction_id = cp_rcv_transaction_id
and jrcvt.organization_id = cp_organization_id --Added by Bgowrava for Bug#7503308
and jrcvt.shipment_header_id = cp_shipment_header_id --Added by Bgowrava for Bug#7503308
and jrcvt.transaction_type = cp_txn_type ;--'DELIVER' --Modified by Bgowrava for Bug#7503308
select po_line_id, organization_id
from rcv_transactions
where transaction_id = p_transaction_id;
select item_id
from po_lines_all
where po_line_id = p_po_line_id;
select regime_id
from JAI_RGM_DEFINITIONS
where regime_code = cp_regime_code ; /* SERVICE or VAT */
select attribute_code tax_type
from JAI_RGM_REGISTRATIONS
where regime_id = cp_regime_id
and registration_type = jai_constants.regn_type_tax_types /* TAX_TYPES */
and attribute_code = cp_tax_type;
SELECT max (line_number)
FROM ap_invoice_lines_all
WHERE invoice_id = inv_id;
SELECT accounting_date
,period_name
,deferred_acctg_flag
,def_acctg_start_date
,def_acctg_end_date
,def_acctg_number_of_periods
,def_acctg_period_type
,set_of_books_id
,wfapproval_status -- Bug 4863208
FROM ap_invoice_lines_all
WHERE invoice_id = inv_id
AND line_number = cpn_max_line_num;
SELECT aia.source
FROM ap_invoices_all aia
WHERE aia.invoice_id = inv_id;
FUNCTION update_payment_schedule (p_total_tax NUMBER) RETURN boolean IS -- bug # 3218978
v_total_tax_in_payment number;
select sum(gross_amount)
from ap_payment_schedules_all
where invoice_id = inv_id;
Fnd_File.put_line(Fnd_File.LOG, 'Start of function update_payment_schedule');
Fnd_File.put_line(Fnd_File.LOG, 'Cannot update payment schedule, total payment amount :'
|| to_char(v_total_payment_amt));
select gross_amount,
payment_num
from ap_payment_schedules_all
where invoice_id = inv_id
order by payment_num
)
loop
v_tax_installment := 0;--Added by nprashar for bug # 13854408
update ap_payment_schedules_all
set gross_amount = gross_amount + v_tax_installment,
amount_remaining = amount_remaining + v_tax_installment,
inv_curr_gross_amount = inv_curr_gross_amount + v_tax_installment,
payment_status_flag = decode(payment_status_flag, 'Y', 'P', payment_status_flag)
-- bug#3624898
where invoice_id = inv_id
and payment_num = v_payment_num;
update ap_payment_schedules_all
set gross_amount = gross_amount + v_diff_tax_amount,
amount_remaining = amount_remaining + v_diff_tax_amount,
inv_curr_gross_amount = inv_curr_gross_amount + v_diff_tax_amount
where invoice_id = inv_id
and payment_num = v_payment_num;
Fnd_File.put_line(Fnd_File.LOG, 'exception from function update_payment_schedule');
end update_payment_schedule; -- bug # 3218978
select amount, invoice_distribution_id
from ap_invoice_distributions_all
WHERE invoice_id = p_price_correct_inv_id
and po_distribution_id = p_po_distribution_id
AND line_type_lookup_code = 'ITEM';
select sum(jam.tax_amount) tax_amount-- project costing fwd porting
from JAI_AP_MATCH_INV_TAXES jam,jai_cmn_taxes_all jct
where p_orig_invoice_id = p_orig_invoice_id
and parent_invoice_distribution_id = p_orig_invoice_dist_id
and jam.tax_id = p_tax_id
--start additions for bug#10167393
/*adhoc tax shall not be apportioned on PRICE CORRECTION*/
and jam.tax_id=jct.tax_id
and (
nvl(jct.adhoc_flag,'N')='Y'
or
( jct.adhoc_flag='Y' and jct.tax_type not in ('Freight','Insurance','Octrai','Other','PURCHASE TAX','ENTRY TAX')));
execute immediate 'select /* price_correct_inv_id */, po_distribution_id, amount, quantity_invoiced
from ap_invoice_lines_all
where invoice_id = :inv_id
and line_number = :inv_line_num'
into cur_price_correct_inv_id, cur_po_distribution_id, cur_amount, cur_quantity_invoiced
USING inv_id, pn_invoice_line_number; -- Using pn_invoice_line_number instead of dist_line_no for Bug#4445989
ship and Update process_online procedure to process reverse charge service tax only
------------------------------------------------------------------------------------------------------- */
jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE, GV_MODULE_PREFIX || '.' || l_api_name ||'.BEGIN', G_PKG_NAME || ': '|| l_api_name || '()+');
SELECT max(line_number)
INTO ln_inv_line_num
FROM ap_invoice_lines_all
WHERE invoice_id = inv_id;
SELECT accts_pay_code_combination_id
INTO apccid
FROM ap_invoices_all
WHERE invoice_id = inv_id;
OPEN for_dist_insertion(inv_id,pn_invoice_line_number, ln_min_dist_line_num);
FETCH for_dist_insertion INTO for_dist_insertion_rec;
CLOSE for_dist_insertion;
p_sob_id => for_dist_insertion_rec.set_of_books_id,
p_organization_id => r_rcv_transactions.organization_id
);*/
SELECT Min(po_distribution_id)
INTO ln_po_dist_id
FROM po_distributions_all
WHERE po_line_id = ln_po_line_id
AND line_location_id = ln_po_line_location_id;
(for_dist_insertion_rec.dist_code_combination_id);
v_assets_tracking_flag := for_dist_insertion_rec.assets_tracking_flag;
ln_lines_to_insert := 1;
ln_lines_to_insert := 2 *ln_lines_to_insert;
, 'ln_lines_to_insert= '||ln_lines_to_insert
|| ',ln_rec_tax_amt= '||ln_rec_tax_amt
|| ',ln_nrec_tax_amt= '||ln_nrec_tax_amt
);
for line in 1..ln_lines_to_insert --line 1 is recoerable line, line 2 is non-recoverable line
loop
IF (NVL(i.inc_tax_flag,'N') = 'N')--exclusive case
THEN
IF line = 1 then--line one for NR, FR, PR tax line
v_tax_amount := nvl(ln_rec_tax_amt, v_tax_amount);
IF for_dist_insertion_rec.assets_tracking_flag = jai_constants.YES THEN
v_assets_tracking_flag := jai_constants.YES;
lv_project_accounting_context := for_dist_insertion_rec.project_accounting_context;
lv_pa_addition_flag := for_dist_insertion_rec.pa_addition_flag;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id;
'Before inserting into ap_invoice_lines_all for line no :' || ln_inv_line_num );
ap_invoice_lines_all_rec.LAST_UPDATED_BY := ln_user_id;
ap_invoice_lines_all_rec.LAST_UPDATE_DATE := sysdate;
ap_invoice_lines_all_rec.LAST_UPDATE_LOGIN := ln_login_id;
Insert_ap_inv_line( p_ap_invoice_lines_all_rec => ap_invoice_lines_all_rec);
/*bug 9346307 - new LOOP added to insert multiple distributions for
same line, if the matched PO shipment has multiple distributions*/
v_distribution_no := 1;
'Before inserting into ap_invoice_distributions_all for distribution line no :'
||v_distribution_no );
ap_invoice_dist_all_rec.last_updated_by := ln_user_id;
ap_invoice_dist_all_rec.last_update_date := sysdate;
ap_invoice_dist_all_rec.last_update_login := ln_login_id;
ap_invoice_dist_all_rec.program_update_date := r_ap_dist.program_update_date;
Insert_ap_inv_dst_line(ap_invoice_dist_all_rec);
jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, GV_MODULE_PREFIX || '.' || l_api_name, 'Before inserting into JAI_AP_MATCH_INV_TAXES ');
INSERT INTO JAI_AP_MATCH_INV_TAXES
(
tax_distribution_id,
assets_tracking_flag,
invoice_id,
po_header_id,
po_line_id,
line_location_id,
set_of_books_id,
exchange_rate,
exchange_rate_type,
exchange_date,
currency_code,
code_combination_id,
last_update_login,
creation_date,
created_by,
last_update_date,
last_updated_by,
acct_pay_code_combination_id,
accounting_date,
tax_id,
tax_amount,
base_amount,
chart_of_accounts_id,
distribution_line_number,
po_distribution_id,
parent_invoice_distribution_id,
legal_entity_id
,INVOICE_LINE_NUMBER
,INVOICE_DISTRIBUTION_ID
,PARENT_INVOICE_LINE_NUMBER
,RCV_TRANSACTION_ID
,LINE_TYPE_LOOKUP_CODE
, recoverable_flag
, line_no
)
VALUES
(
JAI_AP_MATCH_INV_TAXES_S.NEXTVAL,
v_assets_tracking_flag,
inv_id,
cur_items_rec.po_header_id,
cur_items_rec.po_line_id,
cur_items_rec.line_location_id,
cur_items_rec.set_of_books_id,
cur_items_rec.rate,
cur_items_rec.rate_type,
cur_items_rec.rate_date,
cur_items_rec.currency_code,
v_dist_code_combination_id,
cur_items_rec.last_update_login,
cur_items_rec.creation_date,
cur_items_rec.created_by,
cur_items_rec.last_update_date,
cur_items_rec.last_updated_by,
apccid,
cur_items_rec.invoice_date,
i.tax_id,
ROUND(ROUND(v_tax_amt_dist, ln_tax_precision),ln_precision),
ROUND(ROUND(v_tax_amt_dist * r_ap_dist.exchange_rate,ln_tax_precision), ln_precision),
P_caid,
v_distribution_no,
r_ap_dist.po_distribution_id,
r_ap_dist.invoice_distribution_id,
get_ven_info_rec.legal_entity_id
, ln_inv_line_num
, NVL(v_invoice_distribution_id,for_dist_insertion_rec.invoice_distribution_id)
, pn_invoice_line_number
, rcv_tran_id
, lv_misc
, lv_modvat_flag
, i.tax_line_no
);
end loop ;--> for line in 1 to ln_lines_to_insert
jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, GV_MODULE_PREFIX || '.' || l_api_name, '@@Insert liability invoice line. ln_inv_line_num: ' || ln_inv_line_num);
ap_invoice_lines_all_rec.LAST_UPDATED_BY := ln_user_id;
ap_invoice_lines_all_rec.LAST_UPDATE_DATE := sysdate;
ap_invoice_lines_all_rec.LAST_UPDATE_LOGIN := ln_login_id;
Insert_ap_inv_line( ap_invoice_lines_all_rec);
jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, GV_MODULE_PREFIX || '.' || l_api_name, 'Insert liability distribution line '|| v_invoice_distribution_id);
ap_invoice_dist_all_rec.assets_addition_flag := for_dist_insertion_rec.assets_addition_flag;
ap_invoice_dist_all_rec.last_updated_by := ln_user_id;
ap_invoice_dist_all_rec.last_update_date := sysdate;
ap_invoice_dist_all_rec.base_amount := ROUND(ROUND(- v_tax_amount_liability * for_dist_insertion_rec.exchange_rate,ln_tax_precision), ln_precision);
ap_invoice_dist_all_rec.last_update_login := ln_login_id;
ap_invoice_dist_all_rec.match_status_flag := for_dist_insertion_rec.match_status_flag;
ap_invoice_dist_all_rec.reversal_flag := for_dist_insertion_rec.reversal_flag;
ap_invoice_dist_all_rec.program_application_id := for_dist_insertion_rec.program_application_id;
ap_invoice_dist_all_rec.program_id := for_dist_insertion_rec.program_id;
ap_invoice_dist_all_rec.program_update_date := for_dist_insertion_rec.program_update_date;
ap_invoice_dist_all_rec.accts_pay_code_combination_id := for_dist_insertion_rec.accts_pay_code_combination_id;
ap_invoice_dist_all_rec.po_distribution_id := for_dist_insertion_rec.po_distribution_id;
ap_invoice_dist_all_rec.matched_uom_lookup_code := for_dist_insertion_rec.matched_uom_lookup_code;
ap_invoice_dist_all_rec.org_id := for_dist_insertion_rec.org_id;
ap_invoice_dist_all_rec.charge_applicable_to_dist_id := for_dist_insertion_rec.invoice_distribution_id;
ap_invoice_dist_all_rec.dist_match_type := for_dist_insertion_rec.dist_match_type;
Insert_ap_inv_dst_line(ap_invoice_dist_all_rec);
p_parent_dist_id => for_dist_insertion_rec.invoice_distribution_id ,
p_tax_id => r_tax_lines_rec.tax_id
) ;
v_assets_tracking_flag := for_dist_insertion_rec.assets_tracking_flag;
lv_project_accounting_context := for_dist_insertion_rec.project_accounting_context;
lv_pa_addition_flag := for_dist_insertion_rec.pa_addition_flag;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id;
if nvl(for_dist_insertion_rec.invoice_price_variance, -1) <>-1
and
nvl(for_dist_insertion_rec.amount, -1) <> -1 then
v_tax_variance_inv_cur :=
v_tax_amount *
(for_dist_insertion_rec.invoice_price_variance / for_dist_insertion_rec.amount);
v_price_var_accnt := for_dist_insertion_rec.price_var_code_combination_id;
ln_lines_to_insert := 1; -- Loop controller to insert more than one lines for partially recoverable tax lines in PO
ln_lines_to_insert := 2 *ln_lines_to_insert;
jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, GV_MODULE_PREFIX || '.' || l_api_name, 'ln_lines_to_insert='||ln_lines_to_insert||
',ln_rec_tax_amt='||ln_rec_tax_amt ||
',ln_nrec_tax_amt='||ln_nrec_tax_amt
);
jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, GV_MODULE_PREFIX || '.' || l_api_name, 'ln_lines_to_insert ='|| ln_lines_to_insert);
for line in 1..ln_lines_to_insert
loop
jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, GV_MODULE_PREFIX || '.' || l_api_name, 'line = '|| line);
IF for_dist_insertion_rec.assets_tracking_flag = jai_constants.YES THEN
v_assets_tracking_flag := jai_constants.YES;
lv_project_accounting_context := for_dist_insertion_rec.project_accounting_context;
lv_pa_addition_flag := for_dist_insertion_rec.pa_addition_flag;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id;
jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, GV_MODULE_PREFIX || '.' || l_api_name,'Before inserting into ap_invoice_lines_all for line no :'|| ln_inv_line_num );
ap_invoice_lines_all_rec.LAST_UPDATED_BY := ln_user_id;
ap_invoice_lines_all_rec.LAST_UPDATE_DATE := sysdate;
ap_invoice_lines_all_rec.LAST_UPDATE_LOGIN := ln_login_id;
Insert_ap_inv_line( ap_invoice_lines_all_rec);
'Before inserting into ap_invoice_distributions_all for distribution line no :' || v_distribution_no);
ap_invoice_dist_all_rec.accounting_date := for_dist_insertion_rec.accounting_date;
ap_invoice_dist_all_rec.assets_addition_flag := for_dist_insertion_rec.assets_addition_flag;
ap_invoice_dist_all_rec.last_updated_by := for_dist_insertion_rec.last_updated_by;
ap_invoice_dist_all_rec.last_update_date := for_dist_insertion_rec.last_update_date;
ap_invoice_dist_all_rec.period_name := for_dist_insertion_rec.period_name;
ap_invoice_dist_all_rec.set_of_books_id := for_dist_insertion_rec.set_of_books_id;
ap_invoice_dist_all_rec.base_amount := ROUND(ROUND(v_tax_amount * for_dist_insertion_rec.exchange_rate, ln_tax_precision),ln_precision);
ap_invoice_dist_all_rec.created_by := for_dist_insertion_rec.created_by;
ap_invoice_dist_all_rec.creation_date := for_dist_insertion_rec.creation_date;
ap_invoice_dist_all_rec.last_update_login := for_dist_insertion_rec.last_update_login;
ap_invoice_dist_all_rec.match_status_flag := for_dist_insertion_rec.match_status_flag;
ap_invoice_dist_all_rec.reversal_flag := for_dist_insertion_rec.reversal_flag;
ap_invoice_dist_all_rec.program_application_id := for_dist_insertion_rec.program_application_id;
ap_invoice_dist_all_rec.program_id := for_dist_insertion_rec.program_id;
ap_invoice_dist_all_rec.program_update_date := for_dist_insertion_rec.program_update_date;
ap_invoice_dist_all_rec.accts_pay_code_combination_id := for_dist_insertion_rec.accts_pay_code_combination_id;
ap_invoice_dist_all_rec.po_distribution_id := for_dist_insertion_rec.po_distribution_id ;
ap_invoice_dist_all_rec.matched_uom_lookup_code := for_dist_insertion_rec.matched_uom_lookup_code;
ap_invoice_dist_all_rec.org_id := for_dist_insertion_rec.org_id;
ap_invoice_dist_all_rec.charge_applicable_to_dist_id := for_dist_insertion_rec.invoice_distribution_id;
ap_invoice_dist_all_rec.dist_match_type := for_dist_insertion_rec.dist_match_type;
Insert_ap_inv_dst_line(ap_invoice_dist_all_rec);
jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, GV_MODULE_PREFIX || '.' || l_api_name, 'Before inserting into JAI_AP_MATCH_INV_TAXES ');
INSERT INTO JAI_AP_MATCH_INV_TAXES
(
tax_distribution_id,
exchange_rate_variance,
assets_tracking_flag,
invoice_id,
po_header_id,
po_line_id,
line_location_id,
set_of_books_id,
--org_id,
exchange_rate,
exchange_rate_type,
exchange_date,
currency_code,
code_combination_id,
last_update_login,
creation_date,
created_by,
last_update_date,
last_updated_by,
acct_pay_code_combination_id,
accounting_date,
tax_id,
tax_amount,
base_amount,
chart_of_accounts_id,
distribution_line_number,
po_distribution_id,
parent_invoice_distribution_id,
legal_entity_id
,invoice_line_number
,INVOICE_DISTRIBUTION_ID
,PARENT_INVOICE_LINE_NUMBER
,RCV_TRANSACTION_ID
,LINE_TYPE_LOOKUP_CODE
, recoverable_flag
,line_no
)
VALUES
(
JAI_AP_MATCH_INV_TAXES_S.NEXTVAL,
null,
v_assets_tracking_flag,
inv_id,
cur_items_rec.po_header_id, /* All references to r_cur_items_rec have been replaced by cur_items_rec */
cur_items_rec.po_line_id,
cur_items_rec.line_location_id,
cur_items_rec.set_of_books_id,
--cur_items_rec.org_id,
cur_items_rec.rate,
cur_items_rec.rate_type,
cur_items_rec.rate_date,
cur_items_rec.currency_code,
v_dist_code_combination_id,
cur_items_rec.last_update_login,
cur_items_rec.creation_date,
cur_items_rec.created_by,
cur_items_rec.last_update_date,
cur_items_rec.last_updated_by,
apccid,
cur_items_rec.invoice_date,
r_tax_lines_rec.tax_id,
ROUND(ROUND(v_tax_amount,ln_tax_precision),ln_precision),
ROUND(ROUND(v_tax_amount * for_dist_insertion_rec.exchange_rate, ln_tax_precision),ln_precision),
p_caid,
v_distribution_no,
po_dist_id,
for_dist_insertion_rec.invoice_distribution_id,
get_ven_info_rec.legal_entity_id
,ln_inv_line_num
,v_invoice_distribution_id
, pn_invoice_line_number
, rcv_tran_id
, lv_misc
, lv_modvat_flag
,r_tax_lines_rec.tax_line_no
);
end loop ; --> for line in 1 to ln_lines_to_insert Brathod, 5763527
jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, GV_MODULE_PREFIX || '.' || l_api_name,'@@Before inserting liability lines into ap_invoice_lines_all for line no :'|| ln_inv_line_num ||' tax amount = '||lv_tax_line_amount);
ap_invoice_lines_all_rec.LAST_UPDATED_BY := ln_user_id;
ap_invoice_lines_all_rec.LAST_UPDATE_DATE := sysdate;
ap_invoice_lines_all_rec.LAST_UPDATE_LOGIN := ln_login_id;
Insert_ap_inv_line( ap_invoice_lines_all_rec);
'Before inserting liability line into ap_invoice_distributions_all for distribution line no :' || v_distribution_no);
ap_invoice_dist_all_rec.accounting_date := for_dist_insertion_rec.accounting_date;
ap_invoice_dist_all_rec.assets_addition_flag := for_dist_insertion_rec.assets_addition_flag;
ap_invoice_dist_all_rec.last_updated_by := for_dist_insertion_rec.last_updated_by;
ap_invoice_dist_all_rec.last_update_date := for_dist_insertion_rec.last_update_date;
ap_invoice_dist_all_rec.period_name := for_dist_insertion_rec.period_name;
ap_invoice_dist_all_rec.set_of_books_id := for_dist_insertion_rec.set_of_books_id;
ap_invoice_dist_all_rec.base_amount := - ROUND(ROUND(v_tax_amount_liability * for_dist_insertion_rec.exchange_rate, ln_tax_precision),ln_precision);
ap_invoice_dist_all_rec.created_by := for_dist_insertion_rec.created_by;
ap_invoice_dist_all_rec.creation_date := for_dist_insertion_rec.creation_date;
ap_invoice_dist_all_rec.last_update_login := for_dist_insertion_rec.last_update_login;
ap_invoice_dist_all_rec.match_status_flag := for_dist_insertion_rec.match_status_flag ;
ap_invoice_dist_all_rec.reversal_flag := for_dist_insertion_rec.reversal_flag;
ap_invoice_dist_all_rec.program_application_id := for_dist_insertion_rec.program_application_id;
ap_invoice_dist_all_rec.program_id := for_dist_insertion_rec.program_id;
ap_invoice_dist_all_rec.program_update_date := for_dist_insertion_rec.program_update_date;
ap_invoice_dist_all_rec.accts_pay_code_combination_id := for_dist_insertion_rec.accts_pay_code_combination_id;
ap_invoice_dist_all_rec.po_distribution_id := for_dist_insertion_rec.po_distribution_id;
ap_invoice_dist_all_rec.matched_uom_lookup_code := for_dist_insertion_rec.matched_uom_lookup_code;
ap_invoice_dist_all_rec.org_id := for_dist_insertion_rec.org_id;
ap_invoice_dist_all_rec.charge_applicable_to_dist_id := for_dist_insertion_rec.invoice_distribution_id;
ap_invoice_dist_all_rec.dist_match_type := for_dist_insertion_rec.dist_match_type;
Insert_ap_inv_dst_line(ap_invoice_dist_all_rec);
SELECT vendor_id, vendor_site_id, org_id, cancelled_date -- cancelled date added by bug#3206083
,set_of_books_id -- added for bug#3354932
,legal_entity_id -- added rallamse for bug#
,invoice_num -- added by pramasub FP
FROM ap_invoices_all
WHERE invoice_id = v_invoice_id;
SELECT currency_code
FROM gl_sets_of_books
WHERE set_of_books_id = p_sob;
SELECT pay_on_code, pay_on_receipt_summary_code
FROM po_vendor_sites_all
WHERE vendor_id = ven_id
AND vendor_site_id = ven_site_id
AND NVL(org_id, 0) = NVL(v_org_id, 0);
SELECT pod.po_header_id,
pod.po_line_id,
pod.line_location_id,
pod.set_of_books_id,
pod.org_id,
poh.rate,
poh.rate_type,
pod.rate_date,
poh.currency_code,
api.last_update_login,
apd.dist_code_combination_id,
api.creation_date,
api.created_by,
api.last_update_date,
api.last_updated_by,
api.invoice_date
FROM ap_invoices_all api,
ap_invoice_distributions_all apd,
po_distributions_all pod,
po_headers_all poh
WHERE api.invoice_id = inv_id
AND api.invoice_id = apd.invoice_id
AND pod.po_header_id = poh.po_header_id
AND apd.po_distribution_id = pod.po_distribution_id
AND apd.invoice_line_number = line_no
AND apd.distribution_line_number = cpn_max_dist_line_num;
SELECT min(distribution_line_number)
FROM ap_invoice_distributions_all apid
WHERE apid.invoice_id = cpn_invoice_id
AND apid.invoice_line_number = cpn_invoice_line_number;
SELECT max (line_number)
FROM ap_invoice_lines_all
WHERE invoice_id = inv_id;
SELECT accounting_date
,period_name
,deferred_acctg_flag
,def_acctg_start_date
,def_acctg_end_date
,def_acctg_number_of_periods
,def_acctg_period_type
,set_of_books_id
, wfapproval_status
FROM ap_invoice_lines_all
WHERE invoice_id = inv_id
AND line_number = cpn_max_line_num;
SELECT tax_name, tax_account_id, mod_cr_percentage, tax_type,
NVL(rounding_factor,0) rounding_factor /* added by vumaasha for bug 6761425 */
-- bug 3051832, mod_cr_percentage added by aparajita on 10/10/2002 for bug # 2616100
FROM JAI_CMN_TAXES_ALL
WHERE tax_id = t_id;
SELECT batch_id,source
FROM ap_invoices_all
WHERE invoice_id = inv_id;
SELECT org_id, vendor_id, NVL(exchange_rate, 1) exchange_rate, invoice_currency_code
FROM ap_invoices_all
WHERE invoice_id = inv_id;
SELECT accts_pay_code_combination_id
FROM ap_system_parameters_all
WHERE NVL(org_id, 0) = NVL(orgn_id, 0);
CURSOR for_dist_insertion(inv_id NUMBER, inv_line_num NUMBER, cpn_min_dist_line_num NUMBER) IS /* Picks up dtls from std apps inserted line */
SELECT a.accounting_date,a.accrual_posted_flag,
a.assets_addition_flag,a.assets_tracking_flag,
a.cash_posted_flag,a.dist_code_combination_id,
a.last_updated_by,a.last_update_date,
a.line_type_lookup_code,a.period_name,
a.set_of_books_id,a.amount,a.base_amount,a.batch_id,
a.created_by,a.creation_date,a.description,
a.accts_pay_code_combination_id,
a.exchange_rate_variance,
a.last_update_login,a.match_status_flag,
a.posted_flag, a.rate_var_code_combination_id,
a.reversal_flag,a.vat_code,a.exchange_date,
a.exchange_rate, a.exchange_rate_type,
a.price_adjustment_flag,a.program_application_id,
a.program_id, a.program_update_date,
a.global_attribute1, a.global_attribute2, a.global_attribute3,
a.po_distribution_id, a.project_id,a.task_id,a.expenditure_type,
a.expenditure_item_date,a.expenditure_organization_id,
quantity_invoiced,
invoice_distribution_id,
matched_uom_lookup_code,
org_id -- Bug 4863208
, project_accounting_context /* 5763527 */
, pa_addition_flag /* 5763527 */
, asset_book_type_code /* 8406404 */
FROM ap_invoice_distributions_all a
WHERE invoice_id = inv_id
AND invoice_line_number = inv_line_num
AND distribution_line_number = cpn_min_dist_line_num;
SELECT
jrl.tax_amount tax_amount
, jrl.tax_id
, jrl.currency
, jrl.tax_type tax_type
, jrl.modvat_flag
, jrl.tax_line_no
, NVL(jcta.inclusive_tax_flag,'N') inc_tax_flag
FROM
JAI_RCV_LINE_TAXES jrl
, rcv_shipment_lines rsl
, rcv_transactions rt
, jai_cmn_taxes_all jcta
WHERE jrl.shipment_line_id = rsl.shipment_line_id
AND rt.shipment_line_id = rsl.shipment_line_id
AND rt.transaction_id = tran_id
AND jrl.vendor_id = ven_id
AND jcta.tax_id = jrl.tax_id
AND NVL(upper(jrl.tax_type),'TDS') NOT IN ('TDS',
'CVD',
jai_constants.tax_type_add_cvd ,
'CUSTOMS',
jai_constants.tax_type_customs_edu_cess,
jai_constants.tax_type_cvd_edu_cess)
AND NVL(jcta.reverse_charge_flag,'N') = 'Y'
order by tax_line_no
;
SELECT * FROM( --PRAMASUB FP
SELECT
jpt.tax_id
, jpt.tax_amount
, jpt.currency
, jpt.tax_target_amount
, jpt.modvat_flag
, jpt.tax_type
, jpt.tax_line_no
, NVL(jcta.inclusive_tax_flag,'N') inc_tax_flag
FROM
jai_po_taxes jpt
, jai_cmn_taxes_all jcta
WHERE jpt.line_location_id = p_line_location_id
AND jcta.tax_id = jpt.tax_id
AND NVL(upper(jpt.tax_type), 'A') NOT IN ('TDS',
'CVD',
jai_constants.tax_type_add_cvd ,
'CUSTOMS',
jai_constants.tax_type_customs_edu_cess,
jai_constants.tax_type_cvd_edu_cess
)
AND NVL(jpt.vendor_id, 0) = vend_id
AND cp_source <> 'ASBN'
AND NVL(jcta.reverse_charge_flag,'N') = 'Y'
UNION
SELECT
taxes.tax_id
, taxes.TAX_AMT tax_amount
, taxes.CURRENCY_CODE currency
, taxes.TAX_AMT tax_target_amount
, taxes.modvat_flag
, taxes.tax_type
, taxes.tax_line_no
, NVL(jcta.inclusive_tax_flag,'N') inc_tax_flag
FROM
jai_cmn_lines lines
, jai_cmn_document_Taxes taxes
, jai_cmn_taxes_all jcta
WHERE lines.cmn_line_id = taxes.source_doc_line_id
AND taxes.source_doc_type = 'ASBN'
AND lines.po_line_location_id = p_line_location_id
AND lines.shipment_number = cp_shipment_num
AND NVL(taxes.vendor_id, 0) = vend_id
AND jcta.tax_id = taxes.tax_id
AND cp_source = 'ASBN'
AND NVL(jcta.reverse_charge_flag,'N') = 'Y'
)
ORDER BY tax_line_no; --pramasub FP end
SELECT 1
FROM ap_invoice_distributions_all
WHERE invoice_id = p_invoice_id
AND reversal_flag = 'Y'
AND rownum = 1; -- Added by avallabh for bug 4926094 on 03-Feb-2006
SELECT NVL(DISTRIBUTION_LINE_NUMBER,1) FROM
AP_INVOICE_DISTRIBUTIONS_ALL WHERE
INVOICE_ID = inv_id
AND DISTRIBUTION_LINE_NUMBER =
(SELECT MAX(DISTRIBUTION_LINE_NUMBER)
FROM AP_INVOICE_DISTRIBUTIONS_ALL
WHERE INVOICE_ID = inv_id)
FOR UPDATE OF DISTRIBUTION_LINE_NUMBER;
select ap_invoice_distributions_s.nextval
from dual;
select precision
from fnd_currencies
where currency_code = cp_currency_code;
select ship_to_organization_id
,ship_to_location_id
from po_line_locations_all plla
where plla.line_location_id = cpn_line_location_id;
for_dist_insertion_rec for_dist_insertion%ROWTYPE;
v_update_payment_schedule boolean; -- bug#3206083
ln_lines_to_insert number;
select po_line_id, organization_id
from rcv_transactions
where transaction_id = p_transaction_id;
select item_id
from po_lines_all
where po_line_id = p_po_line_id;
select regime_id
from JAI_RGM_DEFINITIONS
where regime_code = cp_regime_code ; /* SERVICE or VAT */
select attribute_code tax_type
from JAI_RGM_REGISTRATIONS
where regime_id = cp_regime_id
and registration_type = jai_constants.regn_type_tax_types /* TAX_TYPES */
and attribute_code = cp_tax_type;
select NVL(rounding_factor,2) -- for bug 16226028
from jai_cmn_taxes_all
where tax_id = cp_tax_id ;
FUNCTION update_payment_schedule (p_total_tax NUMBER) RETURN boolean IS -- bug # 3218978
v_total_tax_in_payment number;
select sum(gross_amount)
from ap_payment_schedules_all
where invoice_id = inv_id;
Fnd_File.put_line(Fnd_File.LOG, 'Start of function update_payment_schedule');
Fnd_File.put_line(Fnd_File.LOG, 'Cannot update payment schedule, total payment amount :'
|| to_char(v_total_payment_amt));
select gross_amount,
payment_num
from ap_payment_schedules_all
where invoice_id = inv_id
order by payment_num
)
loop
v_tax_installment := 0 ;
update ap_payment_schedules_all
set gross_amount = gross_amount + v_tax_installment,
amount_remaining = amount_remaining + v_tax_installment,
inv_curr_gross_amount = inv_curr_gross_amount + v_tax_installment,
payment_status_flag = decode(payment_status_flag, 'Y', 'P', payment_status_flag)
-- bug#3624898
where invoice_id = inv_id
and payment_num = v_payment_num;
update ap_payment_schedules_all
set gross_amount = gross_amount + v_diff_tax_amount,
amount_remaining = amount_remaining + v_diff_tax_amount,
inv_curr_gross_amount = inv_curr_gross_amount + v_diff_tax_amount,
payment_status_flag = decode(payment_status_flag, 'Y', 'P', payment_status_flag)
-- bug#3624898
where invoice_id = inv_id
and payment_num = v_payment_num;
Fnd_File.put_line(Fnd_File.LOG, 'exception from function update_payment_schedule');
end update_payment_schedule; -- bug # 3218978
SELECT chart_of_accounts_id INTO caid
FROM gl_sets_of_books
WHERE set_of_books_id = cur_items_rec.set_of_books_id;
SELECT max(line_number)
INTO ln_inv_line_num
FROM ap_invoice_lines_all
WHERE invoice_id = inv_id;
OPEN for_dist_insertion(inv_id,pn_invoice_line_number, ln_min_dist_line_num);
FETCH for_dist_insertion INTO for_dist_insertion_rec;
CLOSE for_dist_insertion;
v_assets_tracking_flag := for_dist_insertion_rec.assets_tracking_flag;
lv_project_accounting_context := for_dist_insertion_rec.project_accounting_context;
lv_pa_addition_flag := for_dist_insertion_rec.pa_addition_flag;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id;
ln_lines_to_insert := 1; -- Loop controller to insert more than one lines for partially recoverable tax lines in PO
ln_lines_to_insert := 2 *ln_lines_to_insert;
'ln_lines_to_insert='||ln_lines_to_insert||
',ln_rec_tax_amt='||ln_rec_tax_amt ||
',ln_nrec_tax_amt='||ln_nrec_tax_amt
);
for line in 1..ln_lines_to_insert
loop
IF (NVL(tax_lines1_rec.inc_tax_flag,'N') = 'N')--exclusive case
THEN
IF line = 1 then --recoverable part
v_tax_amount := nvl(ln_rec_tax_amt, v_tax_amount);
IF for_dist_insertion_rec.assets_tracking_flag = jai_constants.YES THEN
v_assets_tracking_flag := jai_constants.YES;
lv_project_accounting_context := for_dist_insertion_rec.project_accounting_context;
lv_pa_addition_flag := for_dist_insertion_rec.pa_addition_flag;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id;
jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, GV_MODULE_PREFIX || '.' || l_api_name,'Before inserting into ap_invoice_lines_all for line no :'|| ln_inv_line_num );
ap_invoice_lines_all_rec.LAST_UPDATED_BY := ln_user_id;
ap_invoice_lines_all_rec.LAST_UPDATE_DATE := sysdate;
ap_invoice_lines_all_rec.LAST_UPDATE_LOGIN := ln_login_id;
Insert_ap_inv_line( ap_invoice_lines_all_rec);
jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, GV_MODULE_PREFIX || '.' || l_api_name,'Before inserting into ap_invoice_distributions_all for Invoice distribution id:'|| v_invoice_distribution_id );
ap_invoice_dist_all_rec.assets_addition_flag := for_dist_insertion_rec.assets_addition_flag;
ap_invoice_dist_all_rec.last_updated_by := ln_user_id;
ap_invoice_dist_all_rec.last_update_date := SYSDATE;
ap_invoice_dist_all_rec.base_amount := ROUND(ROUND(v_tax_amount * for_dist_insertion_rec.exchange_rate,ln_tax_precision), ln_precision);
ap_invoice_dist_all_rec.last_update_login := ln_login_id;
ap_invoice_dist_all_rec.match_status_flag := for_dist_insertion_rec.match_status_flag ;
ap_invoice_dist_all_rec.reversal_flag := for_dist_insertion_rec.reversal_flag;
ap_invoice_dist_all_rec.program_application_id := for_dist_insertion_rec.program_application_id;
ap_invoice_dist_all_rec.program_id := for_dist_insertion_rec.program_id;
ap_invoice_dist_all_rec.program_update_date := for_dist_insertion_rec.program_update_date;
ap_invoice_dist_all_rec.accts_pay_code_combination_id := for_dist_insertion_rec.accts_pay_code_combination_id;
ap_invoice_dist_all_rec.org_id := for_dist_insertion_rec.org_id;
ap_invoice_dist_all_rec.po_distribution_id := for_dist_insertion_rec.po_distribution_id;
ap_invoice_dist_all_rec.matched_uom_lookup_code := for_dist_insertion_rec.matched_uom_lookup_code;
ap_invoice_dist_all_rec.charge_applicable_to_dist_id := for_dist_insertion_rec.invoice_distribution_id;
ap_invoice_dist_all_rec.asset_book_type_code := for_dist_insertion_rec.asset_book_type_code;
Insert_ap_inv_dst_line(ap_invoice_dist_all_rec);
jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, GV_MODULE_PREFIX || '.' || l_api_name,'Before inserting into JAI_AP_MATCH_INV_TAXES');
INSERT INTO JAI_AP_MATCH_INV_TAXES
(
tax_distribution_id,
exchange_rate_variance,
assets_tracking_flag,
invoice_id,
po_header_id,
po_line_id,
line_location_id,
set_of_books_id,
exchange_rate,
exchange_rate_type,
exchange_date,
currency_code,
code_combination_id,
last_update_login,
creation_date,
created_by,
last_update_date,
last_updated_by,
acct_pay_code_combination_id,
accounting_date,
tax_id,
tax_amount,
base_amount,
chart_of_accounts_id,
distribution_line_number,
po_distribution_id,
parent_invoice_distribution_id,
legal_entity_id
,INVOICE_LINE_NUMBER
,INVOICE_DISTRIBUTION_ID
,PARENT_INVOICE_LINE_NUMBER
,RCV_TRANSACTION_ID
,LINE_TYPE_LOOKUP_CODE
,recoverable_flag
,line_no
)
VALUES
(
JAI_AP_MATCH_INV_TAXES_S.NEXTVAL,
null,
v_assets_tracking_flag ,
inv_id,
cur_items_rec.po_header_id,
cur_items_rec.po_line_id,
cur_items_rec.line_location_id,
cur_items_rec.set_of_books_id,
cur_items_rec.rate,
cur_items_rec.rate_type,
cur_items_rec.rate_date,
cur_items_rec.currency_code,
v_dist_code_combination_id,
cur_items_rec.last_update_login,
cur_items_rec.creation_date,
cur_items_rec.created_by,
cur_items_rec.last_update_date,
cur_items_rec.last_updated_by,
v_dist_code_combination_id,
cur_items_rec.invoice_date,
tax_lines1_rec.tax_id,
ROUND(ROUND(v_tax_amount,ln_tax_precision),ln_precision),
ROUND(ROUND(v_tax_amount * for_dist_insertion_rec.exchange_rate,ln_tax_precision), ln_precision),
caid,
v_distribution_no,
po_dist_id,
for_dist_insertion_rec.invoice_distribution_id,
get_ven_info_rec.legal_entity_id
, ln_inv_line_num
, NVL(v_invoice_distribution_id,for_dist_insertion_rec.invoice_distribution_id)
, pn_invoice_line_number
, rcv_tran_id
, GV_CONSTANT_MISCELLANEOUS
, lv_modvat_flag
, tax_lines1_rec.tax_line_no
);
END LOOP; --> line in 1..ln_lines_to_insert
jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, GV_MODULE_PREFIX || '.' || l_api_name,'@@Before inserting liability lines into ap_invoice_lines_all for line no :'|| ln_inv_line_num ||' tax amount = '||v_tax_amount_liability);
ap_invoice_lines_all_rec.LAST_UPDATED_BY := ln_user_id;
ap_invoice_lines_all_rec.LAST_UPDATE_DATE := sysdate;
ap_invoice_lines_all_rec.LAST_UPDATE_LOGIN := ln_login_id;
Insert_ap_inv_line( ap_invoice_lines_all_rec);
'@Before inserting liability line into ap_invoice_distributions_all for distribution no :' || v_invoice_distribution_id);
ap_invoice_dist_all_rec.assets_addition_flag := for_dist_insertion_rec.assets_addition_flag;
ap_invoice_dist_all_rec.last_updated_by := ln_user_id;
ap_invoice_dist_all_rec.last_update_date := SYSDATE;
ap_invoice_dist_all_rec.base_amount := ROUND(ROUND(-v_tax_amount_liability * for_dist_insertion_rec.exchange_rate,ln_tax_precision), ln_precision);
ap_invoice_dist_all_rec.last_update_login := ln_login_id;
ap_invoice_dist_all_rec.match_status_flag := for_dist_insertion_rec.match_status_flag ;
ap_invoice_dist_all_rec.reversal_flag := for_dist_insertion_rec.reversal_flag;
ap_invoice_dist_all_rec.program_application_id := for_dist_insertion_rec.program_application_id;
ap_invoice_dist_all_rec.program_id := for_dist_insertion_rec.program_id;
ap_invoice_dist_all_rec.program_update_date := for_dist_insertion_rec.program_update_date;
ap_invoice_dist_all_rec.accts_pay_code_combination_id := for_dist_insertion_rec.accts_pay_code_combination_id;
ap_invoice_dist_all_rec.org_id := for_dist_insertion_rec.org_id;
ap_invoice_dist_all_rec.po_distribution_id := for_dist_insertion_rec.po_distribution_id;
ap_invoice_dist_all_rec.matched_uom_lookup_code := for_dist_insertion_rec.matched_uom_lookup_code;
ap_invoice_dist_all_rec.charge_applicable_to_dist_id := for_dist_insertion_rec.invoice_distribution_id;
ap_invoice_dist_all_rec.asset_book_type_code := for_dist_insertion_rec.asset_book_type_code;
Insert_ap_inv_dst_line(ap_invoice_dist_all_rec);
(Select shipment_num
From rcv_headers_interface
Where invoice_num = get_ven_info_rec.invoice_num)
Loop
v_ship_num := rcv_hdr_intf_rec.shipment_num;
v_assets_tracking_flag := for_dist_insertion_rec.assets_tracking_flag;
lv_project_accounting_context := for_dist_insertion_rec.project_accounting_context;
lv_pa_addition_flag := for_dist_insertion_rec.pa_addition_flag;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id;
ln_lines_to_insert := 1; -- Loop controller to insert more than one lines for partially recoverable tax lines in PO
ln_lines_to_insert := 2 *ln_lines_to_insert;
'ln_lines_to_insert='||ln_lines_to_insert||
',ln_rec_tax_amt='||ln_rec_tax_amt ||
',ln_nrec_tax_amt='||ln_nrec_tax_amt
);
for line in 1..ln_lines_to_insert
loop
IF (NVL(i.inc_tax_flag,'N') = 'N')--exclusive case
THEN
IF line = 1 then
v_tax_amount := nvl(ln_rec_tax_amt, v_tax_amount);
IF for_dist_insertion_rec.assets_tracking_flag = jai_constants.YES THEN
v_assets_tracking_flag := jai_constants.YES;
lv_project_accounting_context := for_dist_insertion_rec.project_accounting_context;
lv_pa_addition_flag := for_dist_insertion_rec.pa_addition_flag;
v_dist_code_combination_id := for_dist_insertion_rec.dist_code_combination_id;
jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, GV_MODULE_PREFIX || '.' || l_api_name,'Before inserting into ap_invoice_lines_all for line no :'|| ln_inv_line_num );
ap_invoice_lines_all_rec.LAST_UPDATED_BY := ln_user_id;
ap_invoice_lines_all_rec.LAST_UPDATE_DATE := sysdate;
ap_invoice_lines_all_rec.LAST_UPDATE_LOGIN := ln_login_id;
Insert_ap_inv_line( ap_invoice_lines_all_rec);
jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, GV_MODULE_PREFIX || '.' || l_api_name,'Before inserting into ap_invoice_distributions_all for Invoice distribution id:'|| v_invoice_distribution_id );
ap_invoice_dist_all_rec.assets_addition_flag := for_dist_insertion_rec.assets_addition_flag;
ap_invoice_dist_all_rec.last_updated_by := ln_user_id;
ap_invoice_dist_all_rec.last_update_date := SYSDATE;
ap_invoice_dist_all_rec.base_amount := ROUND(ROUND(v_tax_amount * for_dist_insertion_rec.exchange_rate,ln_tax_precision), ln_precision);
ap_invoice_dist_all_rec.last_update_login := ln_login_id;
ap_invoice_dist_all_rec.match_status_flag := for_dist_insertion_rec.match_status_flag ;
ap_invoice_dist_all_rec.reversal_flag := for_dist_insertion_rec.reversal_flag;
ap_invoice_dist_all_rec.program_application_id := for_dist_insertion_rec.program_application_id;
ap_invoice_dist_all_rec.program_id := for_dist_insertion_rec.program_id;
ap_invoice_dist_all_rec.program_update_date := for_dist_insertion_rec.program_update_date;
ap_invoice_dist_all_rec.accts_pay_code_combination_id := for_dist_insertion_rec.accts_pay_code_combination_id;
ap_invoice_dist_all_rec.org_id := for_dist_insertion_rec.org_id;
ap_invoice_dist_all_rec.po_distribution_id := for_dist_insertion_rec.po_distribution_id;
ap_invoice_dist_all_rec.matched_uom_lookup_code := for_dist_insertion_rec.matched_uom_lookup_code;
ap_invoice_dist_all_rec.charge_applicable_to_dist_id := for_dist_insertion_rec.invoice_distribution_id;
ap_invoice_dist_all_rec.asset_book_type_code := for_dist_insertion_rec.asset_book_type_code;
Insert_ap_inv_dst_line(ap_invoice_dist_all_rec);
'Before inserting into JAI_AP_MATCH_INV_TAXES tax id ' || i.tax_id );
INSERT INTO JAI_AP_MATCH_INV_TAXES
(
tax_distribution_id,
exchange_rate_variance,
assets_tracking_flag,
invoice_id,
po_header_id,
po_line_id,
line_location_id,
set_of_books_id,
exchange_rate,
exchange_rate_type,
exchange_date,
currency_code,
code_combination_id,
last_update_login,
creation_date,
created_by,
last_update_date,
last_updated_by,
acct_pay_code_combination_id,
accounting_date,
tax_id,
tax_amount,
base_amount,
chart_of_accounts_id,
distribution_line_number,
po_distribution_id,
parent_invoice_distribution_id,
legal_entity_id
,INVOICE_LINE_NUMBER
,INVOICE_DISTRIBUTION_ID
,PARENT_INVOICE_LINE_NUMBER
,RCV_TRANSACTION_ID
,LINE_TYPE_LOOKUP_CODE
,recoverable_flag
,line_no
)
VALUES
(
JAI_AP_MATCH_INV_TAXES_S.NEXTVAL,
null,
v_assets_tracking_flag ,
inv_id,
cur_items_rec.po_header_id,
cur_items_rec.po_line_id,
cur_items_rec.line_location_id,
cur_items_rec.set_of_books_id,
cur_items_rec.rate,
cur_items_rec.rate_type,
cur_items_rec.rate_date,
cur_items_rec.currency_code,
v_dist_code_combination_id,
cur_items_rec.last_update_login,
cur_items_rec.creation_date,
cur_items_rec.created_by,
cur_items_rec.last_update_date,
cur_items_rec.last_updated_by,
v_dist_code_combination_id,
cur_items_rec.invoice_date,
i.tax_id,
ROUND(ROUND(lv_tax_line_amount,ln_tax_precision),ln_precision),
ROUND(ROUND(i.tax_amount, ln_tax_precision),ln_precision),
caid,
v_distribution_no,
po_dist_id,
for_dist_insertion_rec.invoice_distribution_id,
get_ven_info_rec.legal_entity_id
, DECODE ( NVL(i.inc_tax_flag,'N')
, 'N',ln_inv_line_num
, 'Y',pn_invoice_line_number
)
,NVL(v_invoice_distribution_id,for_dist_insertion_rec.invoice_distribution_id)
, pn_invoice_line_number
, rcv_tran_id
, GV_CONSTANT_MISCELLANEOUS
, lv_modvat_flag
, i.tax_line_no
);
jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, GV_MODULE_PREFIX || '.' || l_api_name,'@@Before inserting liability lines into ap_invoice_lines_all for line no :'|| ln_inv_line_num ||' tax amount = '||lv_tax_line_amount);
ap_invoice_lines_all_rec.LAST_UPDATED_BY := ln_user_id;
ap_invoice_lines_all_rec.LAST_UPDATE_DATE := sysdate;
ap_invoice_lines_all_rec.LAST_UPDATE_LOGIN := ln_login_id;
Insert_ap_inv_line( ap_invoice_lines_all_rec);
'@Before inserting liability line into ap_invoice_distributions_all for distribution no :' || v_invoice_distribution_id);
ap_invoice_dist_all_rec.assets_addition_flag := for_dist_insertion_rec.assets_addition_flag;
ap_invoice_dist_all_rec.last_updated_by := ln_user_id;
ap_invoice_dist_all_rec.last_update_date := SYSDATE;
ap_invoice_dist_all_rec.base_amount := ROUND(ROUND(-v_tax_amount_liability * for_dist_insertion_rec.exchange_rate,ln_tax_precision), ln_precision);
ap_invoice_dist_all_rec.last_update_login := ln_login_id;
ap_invoice_dist_all_rec.match_status_flag := for_dist_insertion_rec.match_status_flag ;
ap_invoice_dist_all_rec.reversal_flag := for_dist_insertion_rec.reversal_flag;
ap_invoice_dist_all_rec.program_application_id := for_dist_insertion_rec.program_application_id;
ap_invoice_dist_all_rec.program_id := for_dist_insertion_rec.program_id;
ap_invoice_dist_all_rec.program_update_date := for_dist_insertion_rec.program_update_date;
ap_invoice_dist_all_rec.accts_pay_code_combination_id := for_dist_insertion_rec.accts_pay_code_combination_id;
ap_invoice_dist_all_rec.org_id := for_dist_insertion_rec.org_id;
ap_invoice_dist_all_rec.po_distribution_id := for_dist_insertion_rec.po_distribution_id;
ap_invoice_dist_all_rec.matched_uom_lookup_code := for_dist_insertion_rec.matched_uom_lookup_code;
ap_invoice_dist_all_rec.charge_applicable_to_dist_id := for_dist_insertion_rec.invoice_distribution_id;
ap_invoice_dist_all_rec.asset_book_type_code := for_dist_insertion_rec.asset_book_type_code;
Insert_ap_inv_dst_line(ap_invoice_dist_all_rec);
'update_payment_schedule(cum_tax_amt): amount add' || cum_tax_amt);
v_update_payment_schedule:=update_payment_schedule(cum_tax_amt);
UPDATE ap_invoices_all
SET invoice_amount = invoice_amount + cum_tax_amt,
approved_amount = approved_amount + cum_tax_amt,
pay_curr_invoice_amount = pay_curr_invoice_amount + cum_tax_amt,
amount_applicable_to_discount = amount_applicable_to_discount + cum_tax_amt,
payment_status_flag = decode(payment_status_flag, 'Y', 'P', payment_status_flag)
WHERE invoice_id = inv_id;
update ap_invoices_all
set base_amount = invoice_amount * exchange_rate
where invoice_id = inv_id;