[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_debug_info := 'Select Batch Info...';
select tag INTO l_encoding from fnd_lookup_values
where lookup_type = 'FND_ISO_CHARACTER_SET_MAP'
and lookup_code = ( select value from v$nls_parameters where parameter='NLS_CHARACTERSET')
and language='US' ;
'SELECT HOU.NAME AS OPERATING_UNIT,
FNA.AGREEMENT_NAME,
FNA.AGREEMENT_START_DATE,
FNA.AGREEMENT_END_DATE,
FNA.PARTNER_REFERENCE,
CBA.BANK_ACCOUNT_NAME,
DECODE(FNA.SEL_REC_PAST_DUE_TXNS_FLAG, ''Y'', ''Yes'', ''N'', ''No'') AS SELECT_REC_PAST_DUE_TXNS,
FNA.DAYS_PAST_DUE,
FLC1.MEANING AS NETTING_ORDER_RULE,
FLC2.MEANING AS NETTING_BALANCE_RULE,
FLC3.MEANING AS NETTING_CURRENCY_RULE,
FNA.NET_CURRENCY_CODE,
GLC.USER_CONVERSION_TYPE AS EXCHANGE_RATE_TYPE,
FNB.EXCHANGE_RATE,
FNB.BATCH_NUMBER,
FNB.BATCH_NAME,
FNB.BATCH_CURRENCY,
FNB.SETTLEMENT_DATE,
FNB.TRANSACTION_DUE_DATE,
FNB.RESPONSE_DATE,
FNB.TOTAL_NETTED_AMT
FROM FUN_NET_BATCHES_ALL FNB,
FUN_NET_AGREEMENTS_ALL FNA,
HR_OPERATING_UNITS HOU,
CE_BANK_ACCOUNTS CBA,
FUN_LOOKUPS FLC1,
FUN_LOOKUPS FLC2,
FUN_LOOKUPS FLC3,
gl_daily_conversion_types glc
WHERE FNA.AGREEMENT_ID = FNB.AGREEMENT_ID
AND GLC.CONVERSION_TYPE = FNB.EXCHANGE_RATE_TYPE
AND HOU.ORGANIZATION_ID = FNB.ORG_ID
AND CBA.BANK_ACCOUNT_ID = FNA.BANK_ACCOUNT_ID
AND FLC1.LOOKUP_TYPE = ''FUN_NET_ORDER_RULE''
AND FLC1.LOOKUP_CODE = FNA.NET_ORDER_RULE_CODE
AND FLC2.LOOKUP_TYPE = ''FUN_NET_BALANCE_RULE''
AND FLC2.LOOKUP_CODE = FNA.NET_BALANCE_RULE_CODE
AND FLC3.LOOKUP_TYPE = ''FUN_NET_CURRENCY_RULE''
AND FLC3.LOOKUP_CODE = FNA.NET_CURRENCY_RULE_CODE
AND FNB.BATCH_ID = :BATCH_ID');
l_debug_info := 'Select AP invoices...';
SELECT distinct
PV.VENDOR_ID AS SUPPLIER_ID,
PVS.VENDOR_SITE_ID AS SITE_ID,
PV.VENDOR_NAME AS SUPPLIER_NAME,
PV.SEGMENT1 AS SUPPLIER_NUM,
PVS.VENDOR_SITE_CODE AS SITE,
PV.NUM_1099 AS SUPPLIER_TAXPAYER_ID,
PV.VAT_REGISTRATION_NUM AS SUPPLIER_TAX_REGN_NUM
FROM FUN_NET_AP_INVS_ALL FNAP,
FUN_NET_BATCHES_ALL FNB,
AP_INVOICES_ALL API,
AP_LOOKUP_CODES ALC,
PO_VENDORS PV,
PO_VENDOR_SITES_ALL PVS
WHERE FNAP.BATCH_ID = p_batch_id
AND FNAP.BATCH_ID = FNB.BATCH_ID
AND FNAP.INVOICE_ID = API.INVOICE_ID
AND ALC.LOOKUP_CODE = API.INVOICE_TYPE_LOOKUP_CODE
AND ALC.LOOKUP_TYPE = 'INVOICE TYPE'
AND PV.VENDOR_ID = API.VENDOR_ID
AND PVS.VENDOR_SITE_ID = API.VENDOR_SITE_ID
ORDER BY PV.VENDOR_NAME,
PVS.VENDOR_SITE_CODE
) loop
put_starttag('SUPPLIER_RECORD');
'SELECT
API.INVOICE_NUM,
ALC.DISPLAYED_FIELD AS INVOICE_TYPE,
API.INVOICE_DATE AS INVOICE_DATE,
API.INVOICE_AMOUNT,
FNAP.INV_CURR_OPEN_AMT AS INVOICE_CURRENCY_OPEN_AMOUNT,
API.INVOICE_CURRENCY_CODE AS INVOICE_CURRENCY,
FNAP.OPEN_AMT AS INV_RECKONING_OPEN_AMOUNT,
FNB.BATCH_CURRENCY AS RECKONING_CURRENCY,
MIN(APS.DUE_DATE) AS DUE_DATE,
SUM(nvl(vat.vat_amount,0)) AS VAT_AMOUNT
FROM FUN_NET_AP_INVS_ALL FNAP,
FUN_NET_BATCHES_ALL FNB,
AP_INVOICES_ALL API,
ap_invoice_lines_all ail,
AP_LOOKUP_CODES ALC,
AP_PAYMENT_SCHEDULES_ALL APS,
PO_VENDORS PV,
PO_VENDOR_SITES_ALL PVS,
(select ail2.invoice_id
,sum(ail2.amount) vat_amount
from ap_invoices_all ai2
,ap_invoice_lines_all ail2
,ap_tax_codes_all atc
where ai2.vendor_id = :SUPPLIER_ID
and ai2.vendor_site_id = :SITE_ID
and ail2.invoice_id = ai2.invoice_id
and ail2.line_type_lookup_code = ''TAX''
and atc.name = ail2.tax_classification_code
and atc.tax_type = ''SALES''
and atc.org_id = ail2.org_id
group by ail2.invoice_id
) vat
WHERE FNAP.INVOICE_ID = API.INVOICE_ID
AND FNAP.BATCH_ID = FNB.BATCH_ID
AND ALC.LOOKUP_CODE = API.INVOICE_TYPE_LOOKUP_CODE
AND ALC.LOOKUP_TYPE = ''INVOICE TYPE''
AND APS.INVOICE_ID = API.INVOICE_ID
AND PV.VENDOR_ID = API.VENDOR_ID
AND PVS.VENDOR_SITE_ID = API.VENDOR_SITE_ID
AND FNAP.BATCH_ID = :BATCH_ID
AND PV.VENDOR_ID = :SUPPLIER_ID
AND PVS.VENDOR_SITE_ID = :SITE_ID
and vat.invoice_id(+) = API.INVOICE_ID
GROUP BY
API.INVOICE_NUM,
ALC.DISPLAYED_FIELD,
API.INVOICE_DATE,
API.INVOICE_AMOUNT,
FNAP.INV_CURR_OPEN_AMT,
API.INVOICE_CURRENCY_CODE,
FNAP.OPEN_AMT,
FNB.BATCH_CURRENCY
ORDER BY
API.INVOICE_NUM');
l_debug_info := 'Select AR transactions...';
SELECT distinct
HP.PARTY_NAME AS CUSTOMER,
HCA.CUST_ACCOUNT_ID AS CUST_ACCOUNT_ID,
HCA.ACCOUNT_NUMBER AS CUSTOMER_NUMBER,
HCSU.LOCATION,
HCSU.SITE_USE_ID AS SITE_USE_ID,
HP.JGZZ_FISCAL_CODE AS CUST_TAXPAYER_ID,
HP.TAX_REFERENCE AS CUST_TAX_REGN_NUM
FROM FUN_NET_AR_TXNS_ALL FNAR,
FUN_NET_BATCHES_ALL FNB,
RA_CUSTOMER_TRX_ALL RCT,
RA_CUST_TRX_TYPES_ALL RCTT,
HZ_CUST_ACCOUNTS_ALL HCA,
HZ_PARTIES HP,
HZ_CUST_SITE_USES_ALL HCSU
WHERE FNAR.CUSTOMER_TRX_ID = RCT.CUSTOMER_TRX_ID
AND FNAR.BATCH_ID = FNB.BATCH_ID
AND RCTT.CUST_TRX_TYPE_ID = RCT.CUST_TRX_TYPE_ID
AND RCT.ORG_ID = RCTT.ORG_ID
AND HCA.CUST_ACCOUNT_ID = RCT.BILL_TO_CUSTOMER_ID
AND HP.PARTY_ID = HCA.PARTY_ID
AND HCSU.SITE_USE_ID = RCT.BILL_TO_SITE_USE_ID
AND FNAR.BATCH_ID = p_batch_id
ORDER BY HP.PARTY_NAME,
HCSU.LOCATION
) loop
put_starttag('CUSTOMER_RECORD');
'SELECT
RCT.TRX_NUMBER,
RCTT.NAME AS TRX_TYPE,
RCT.TRX_DATE,
SUM(APS.AMOUNT_DUE_ORIGINAL) AS TRX_AMOUNT,
APS.INVOICE_CURRENCY_CODE AS TRX_CURRENCY,
FNAR.TXN_CURR_OPEN_AMT AS TXN_CURR_OPEN_AMOUNT,
FNB.BATCH_CURRENCY AS RECKONING_CURRENCY,
FNAR.OPEN_AMT AS TRX_RECKONING_OPEN_AMOUNT,
MIN(APS.DUE_DATE) AS DUE_DATE,
sum(nvl(vat_amount,0)) AS VAT_AMOUNT -- Russian Requirement
FROM FUN_NET_AR_TXNS_ALL FNAR,
FUN_NET_BATCHES_ALL FNB,
RA_CUSTOMER_TRX_ALL RCT,
RA_CUST_TRX_TYPES_ALL RCTT,
AR_PAYMENT_SCHEDULES_ALL APS,
HZ_CUST_ACCOUNTS_ALL HCA,
HZ_PARTIES HP,
HZ_CUST_SITE_USES_ALL HCSU,
-- Russian Requirement
(select
rctl2.customer_trx_id
,sum(rctl2.extended_amount) AS VAT_AMOUNT
from
ra_customer_trx_all rct2
,ra_customer_trx_lines_all rctl2
,ar_vat_tax_all_b avt
where
rct2.bill_to_customer_id=:CUST_ACCOUNT_ID
and rct2.bill_to_site_use_id=:SITE_USE_ID
and rctl2.customer_trx_id=rct2.customer_trx_id
and rctl2.line_type = ''TAX''
and avt.vat_tax_id = rctl2.vat_tax_id
and avt.tax_type = ''VAT''
group by rctl2.customer_trx_id
) rctl3
WHERE FNAR.CUSTOMER_TRX_ID = RCT.CUSTOMER_TRX_ID
AND FNAR.BATCH_ID = FNB.BATCH_ID
AND RCTT.CUST_TRX_TYPE_ID = RCT.CUST_TRX_TYPE_ID
AND RCT.ORG_ID = RCTT.ORG_ID
AND APS.CUSTOMER_TRX_ID = RCT.CUSTOMER_TRX_ID
AND HCA.CUST_ACCOUNT_ID = RCT.BILL_TO_CUSTOMER_ID
AND HP.PARTY_ID = HCA.PARTY_ID
AND HCSU.SITE_USE_ID = RCT.BILL_TO_SITE_USE_ID
AND FNAR.BATCH_ID = :BATCH_ID
-- Detail mode
AND RCT.BILL_TO_CUSTOMER_ID = :CUST_ACCOUNT_ID
AND RCT.BILL_TO_SITE_USE_ID = :SITE_USE_ID
-- Russian Requirement
and rctl3.customer_trx_id(+) = rct.customer_trx_id
GROUP BY
RCT.TRX_NUMBER,
RCTT.NAME,
RCT.TRX_DATE,
APS.INVOICE_CURRENCY_CODE,
FNAR.TXN_CURR_OPEN_AMT,
FNB.BATCH_CURRENCY,
FNAR.OPEN_AMT
ORDER BY
RCT.TRX_NUMBER');
l_debug_info := 'Select Batch Info...';
select tag INTO l_encoding from fnd_lookup_values
where lookup_type = 'FND_ISO_CHARACTER_SET_MAP'
and lookup_code = ( select value from v$nls_parameters where parameter='NLS_CHARACTERSET')
and language='US' ;
'SELECT HOU.NAME AS OPERATING_UNIT,
FNA.AGREEMENT_NAME,
FNA.AGREEMENT_START_DATE,
FNA.AGREEMENT_END_DATE,
FNA.PARTNER_REFERENCE,
CBA.BANK_ACCOUNT_NAME,
-- Dropped; DECODE(FNA.APPLY_EARNED_DISCOUNT
DECODE(FNA.SEL_REC_PAST_DUE_TXNS_FLAG, ''Y'', ''Yes'', ''N'', ''No'') AS SELECT_REC_PAST_DUE_TXNS,
FNA.DAYS_PAST_DUE,
FLC1.MEANING AS NETTING_ORDER_RULE,
FLC2.MEANING AS NETTING_BALANCE_RULE,
FLC3.MEANING AS NETTING_CURRENCY_RULE,
FNA.NET_CURRENCY_CODE,
GLC.USER_CONVERSION_TYPE AS EXCHANGE_RATE_TYPE,
FNB.EXCHANGE_RATE,
FNB.BATCH_NUMBER,
FNB.BATCH_NAME,
FNB.BATCH_CURRENCY,
FNB.SETTLEMENT_DATE,
FNB.TRANSACTION_DUE_DATE,
FNB.RESPONSE_DATE,
FNB.TOTAL_NETTED_AMT
FROM FUN_NET_BATCHES_ALL FNB,
FUN_NET_AGREEMENTS_ALL FNA,
HR_OPERATING_UNITS HOU,
CE_BANK_ACCOUNTS CBA,
FUN_LOOKUPS FLC1,
FUN_LOOKUPS FLC2,
FUN_LOOKUPS FLC3,
gl_daily_conversion_types GLC
WHERE FNA.AGREEMENT_ID = FNB.AGREEMENT_ID
AND GLC.CONVERSION_TYPE = FNB.EXCHANGE_RATE_TYPE
AND HOU.ORGANIZATION_ID = FNB.ORG_ID
AND CBA.BANK_ACCOUNT_ID = FNA.BANK_ACCOUNT_ID
AND FLC1.LOOKUP_TYPE = ''FUN_NET_ORDER_RULE''
AND FLC1.LOOKUP_CODE = FNA.NET_ORDER_RULE_CODE
AND FLC2.LOOKUP_TYPE = ''FUN_NET_BALANCE_RULE''
AND FLC2.LOOKUP_CODE = FNA.NET_BALANCE_RULE_CODE
AND FLC3.LOOKUP_TYPE = ''FUN_NET_CURRENCY_RULE''
AND FLC3.LOOKUP_CODE = FNA.NET_CURRENCY_RULE_CODE
AND FNB.BATCH_ID = :BATCH_ID');
l_debug_info := 'Select AP invoices...';
SELECT distinct
PV.VENDOR_ID AS SUPPLIER_ID,
PVS.VENDOR_SITE_ID AS SITE_ID,
PV.VENDOR_NAME AS SUPPLIER_NAME,
PV.SEGMENT1 AS SUPPLIER_NUM,
PVS.VENDOR_SITE_CODE AS SITE,
PV.NUM_1099 AS SUPPLIER_TAXPAYER_ID,
PV.VAT_REGISTRATION_NUM AS SUPPLIER_TAX_REGN_NUM
FROM FUN_NET_AP_INVS_ALL FNAP,
FUN_NET_BATCHES_ALL FNB,
AP_INVOICES_ALL API,
AP_LOOKUP_CODES ALC,
PO_VENDORS PV,
PO_VENDOR_SITES_ALL PVS
WHERE FNAP.BATCH_ID = p_batch_id
AND FNAP.BATCH_ID = FNB.BATCH_ID
AND FNAP.INVOICE_ID = API.INVOICE_ID
AND ALC.LOOKUP_CODE = API.INVOICE_TYPE_LOOKUP_CODE
AND ALC.LOOKUP_TYPE = 'INVOICE TYPE'
AND PV.VENDOR_ID = API.VENDOR_ID
AND PVS.VENDOR_SITE_ID = API.VENDOR_SITE_ID
ORDER BY PV.VENDOR_NAME,
PVS.VENDOR_SITE_CODE
) loop
put_starttag('SUPPLIER_RECORD');
'SELECT
API.INVOICE_NUM,
ALC.DISPLAYED_FIELD AS INVOICE_TYPE,
API.INVOICE_DATE AS INVOICE_DATE,
API.INVOICE_AMOUNT AS INVOICE_AMOUNT,
FNAP.INV_CURR_NET_AMT AS NETTED_AMT_INV_CURR,
API.INVOICE_CURRENCY_CODE AS INVOICE_CURRENCY,
FNAP.NETTED_AMT AS NETTED_AMT_BATCH_CURR,
FNB.BATCH_CURRENCY AS RECKONING_CURRENCY,
AC.CHECK_NUMBER AS PAYMENT_NUMBER,
SUM(nvl(vat.vat_amount,0)) AS VAT_AMOUNT
FROM FUN_NET_AP_INVS_ALL FNAP,
FUN_NET_BATCHES_ALL FNB,
AP_INVOICES_ALL API,
AP_LOOKUP_CODES ALC,
PO_VENDORS PV,
PO_VENDOR_SITES_ALL PVS,
AP_CHECKS_ALL AC,
(select ail2.invoice_id
,sum(ail2.amount) vat_amount
from ap_invoices_all ai2
,ap_invoice_lines_all ail2
,ap_tax_codes_all atc
where ai2.vendor_id = :SUPPLIER_ID
and ai2.vendor_site_id = :SITE_ID
and ail2.invoice_id = ai2.invoice_id
and ail2.line_type_lookup_code = ''TAX''
and atc.name = ail2.tax_classification_code
and atc.tax_type = ''SALES''
and atc.org_id = ail2.org_id
group by ail2.invoice_id
) vat
WHERE FNAP.INVOICE_ID = API.INVOICE_ID
AND FNAP.BATCH_ID = FNB.BATCH_ID
AND ALC.LOOKUP_CODE = API.INVOICE_TYPE_LOOKUP_CODE
AND ALC.LOOKUP_TYPE = ''INVOICE TYPE''
AND PV.VENDOR_ID = API.VENDOR_ID
AND PVS.VENDOR_SITE_ID = API.VENDOR_SITE_ID
AND AC.CHECK_ID = FNAP.CHECK_ID(+)
AND FNAP.BATCH_ID = :BATCH_ID
AND PV.VENDOR_ID = :SUPPLIER_ID
AND PVS.VENDOR_SITE_ID = :SITE_ID
and vat.invoice_id(+) = API.INVOICE_ID
GROUP BY
API.INVOICE_NUM,
ALC.DISPLAYED_FIELD,
API.INVOICE_DATE,
API.INVOICE_AMOUNT,
FNAP.INV_CURR_NET_AMT,
API.INVOICE_CURRENCY_CODE,
FNAP.NETTED_AMT,
FNB.BATCH_CURRENCY,
AC.CHECK_NUMBER
ORDER BY
API.INVOICE_NUM');
l_debug_info := 'Select AR transactions...';
SELECT distinct
HP.PARTY_NAME AS CUSTOMER,
HCA.CUST_ACCOUNT_ID AS CUST_ACCOUNT_ID,
HCA.ACCOUNT_NUMBER CUSTOMER_NUMBER,
HCSU.LOCATION,
HCSU.SITE_USE_ID AS SITE_USE_ID,
HP.JGZZ_FISCAL_CODE AS CUST_TAXPAYER_ID,
HP.TAX_REFERENCE AS CUST_TAX_REGN_NUM
FROM FUN_NET_AR_TXNS_ALL FNAR,
FUN_NET_BATCHES_ALL FNB,
RA_CUSTOMER_TRX_ALL RCT,
RA_CUSTOMER_TRX_LINES_ALL RCTL,
RA_CUST_TRX_TYPES_ALL RCTT,
HZ_CUST_ACCOUNTS_ALL HCA,
HZ_PARTIES HP,
HZ_CUST_SITE_USES_ALL HCSU,
AR_CASH_RECEIPTS_ALL ACR
WHERE FNAR.CUSTOMER_TRX_ID = RCT.CUSTOMER_TRX_ID
AND RCT.CUSTOMER_TRX_ID = RCTL.CUSTOMER_TRX_ID
AND RCTT.CUST_TRX_TYPE_ID = RCT.CUST_TRX_TYPE_ID
AND RCT.ORG_ID = RCTT.ORG_ID
AND HCA.CUST_ACCOUNT_ID = RCT.BILL_TO_CUSTOMER_ID
AND HP.PARTY_ID = HCA.PARTY_ID
AND HCSU.SITE_USE_ID = RCT.BILL_TO_SITE_USE_ID
AND ACR.CASH_RECEIPT_ID = FNAR.CASH_RECEIPT_ID
AND FNB.BATCH_ID = FNAR.BATCH_ID
AND FNAR.BATCH_ID = p_batch_id
ORDER BY HP.PARTY_NAME,
HCSU.LOCATION
) loop
put_starttag('CUSTOMER_RECORD');
'SELECT
RCT.TRX_NUMBER,
RCTT.NAME AS TRX_TYPE,
RCT.TRX_DATE,
SUM(RCTL.EXTENDED_AMOUNT) AS TRX_AMOUNT,
FNAR.TXN_CURR_NET_AMT AS NETTED_AMT_TRX_CURR,
RCT.INVOICE_CURRENCY_CODE AS TRX_CURRENCY,
FNAR.NETTED_AMT AS NETTED_AMT_RECKONING_CURR,
FNB.BATCH_CURRENCY AS RECKONING_CURRENCY,
ACR.RECEIPT_NUMBER,
sum(nvl(vat_amount,0)) AS VAT_AMOUNT -- Russian Requirement
FROM FUN_NET_AR_TXNS_ALL FNAR,
RA_CUSTOMER_TRX_ALL RCT,
RA_CUSTOMER_TRX_LINES_ALL RCTL,
RA_CUST_TRX_TYPES_ALL RCTT,
HZ_CUST_ACCOUNTS_ALL HCA,
HZ_PARTIES HP,
HZ_CUST_SITE_USES_ALL HCSU,
AR_CASH_RECEIPTS_ALL ACR,
FUN_NET_BATCHES_ALL FNB,
-- Russian Requirement
(select
rctl2.customer_trx_id
,sum(rctl2.extended_amount) AS VAT_AMOUNT
from
ra_customer_trx_all rct2
,ra_customer_trx_lines_all rctl2
,ar_vat_tax_all_b avt
where
rct2.bill_to_customer_id=:CUST_ACCOUNT_ID
and rct2.bill_to_site_use_id=:SITE_USE_ID
and rctl2.customer_trx_id=rct2.customer_trx_id
and rctl2.line_type = ''TAX''
and avt.vat_tax_id = rctl2.vat_tax_id
and avt.tax_type = ''VAT''
group by rctl2.customer_trx_id
) rctl3
WHERE FNAR.CUSTOMER_TRX_ID = RCT.CUSTOMER_TRX_ID
AND RCT.CUSTOMER_TRX_ID = RCTL.CUSTOMER_TRX_ID
AND RCTT.CUST_TRX_TYPE_ID = RCT.CUST_TRX_TYPE_ID
AND RCT.ORG_ID = RCTT.ORG_ID
AND HCA.CUST_ACCOUNT_ID = RCT.BILL_TO_CUSTOMER_ID
AND HP.PARTY_ID = HCA.PARTY_ID
AND HCSU.SITE_USE_ID = RCT.BILL_TO_SITE_USE_ID
AND ACR.CASH_RECEIPT_ID = FNAR.CASH_RECEIPT_ID
AND FNB.BATCH_ID = FNAR.BATCH_ID
AND FNAR.BATCH_ID = :BATCH_ID
-- Detail mode
AND RCT.BILL_TO_CUSTOMER_ID = :CUST_ACCOUNT_ID
AND RCT.BILL_TO_SITE_USE_ID = :SITE_USE_ID
-- Russian Requirement
and rctl3.customer_trx_id(+) = rct.customer_trx_id
GROUP BY
RCT.TRX_NUMBER,
RCTT.NAME,
RCT.TRX_DATE,
FNAR.TXN_CURR_NET_AMT,
RCT.INVOICE_CURRENCY_CODE,
FNAR.NETTED_AMT,
FNB.BATCH_CURRENCY,
ACR.RECEIPT_NUMBER
ORDER BY
RCT.TRX_NUMBER');