[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
jgtla.invoice_type
INTO
l_invoice_type
FROM
ar_gta_tax_limits_all jgtla
,ar_gta_type_mappings jgtm
,ra_customer_trx_all rcta
WHERE rcta.customer_trx_id = p_customer_trx_id
AND rcta.cust_trx_type_id = jgtm.transaction_type_id
AND jgtm.limitation_id = jgtla.limitation_id
AND jgtla.fp_tax_registration_number = p_fp_tax_registration_num
AND jgtla.org_id = p_org_id;
SELECT HCA.CLASS_CODE
INTO l_class_code
FROM ra_customer_trx_all h,
hz_cust_site_uses_all hcsua,
zx_party_tax_profile ZPTP,
hz_code_assignments HCA,
HZ_CUST_ACCT_SITES_ALL HCASA
WHERE h.bill_to_site_use_id=hcsua.SITE_USE_ID
AND hcsua.cust_acct_site_id=HCASA.CUST_ACCT_SITE_ID
AND HCASA.PARTY_SITE_ID=ZPTP.PARTY_ID
AND ZPTP.PARTY_TYPE_CODE = 'THIRD_PARTY_SITE'
--AND ZPTP.CUSTOMER_FLAG = 'Y'
AND ZPTP.PARTY_TAX_PROFILE_ID = HCA.OWNER_TABLE_ID
AND HCA.OWNER_TABLE_NAME = 'ZX_PARTY_TAX_PROFILE'
AND HCA.CLASS_CATEGORY = 'AR_GTA_TAXPAYER_TYPE'
AND SYSDATE BETWEEN HCA.START_DATE_ACTIVE AND NVL(HCA.END_DATE_ACTIVE,SYSDATE+1)
AND h.customer_trx_id = p_customer_trx_id;
SELECT HCA.CLASS_CODE
INTO l_class_code
FROM ra_customer_trx_all h,
Hz_Parties RAC_BILL_PARTY,
Hz_Cust_Accounts RAC_BILL,
zx_party_tax_profile ZPTP,
hz_code_assignments HCA
WHERE h.bill_to_customer_id = RAC_BILL.CUST_ACCOUNT_ID
AND rac_bill.party_id = RAC_BILL_PARTY.Party_Id
AND ZPTP.PARTY_ID = RAC_BILL_PARTY.Party_Id
AND ZPTP.PARTY_TYPE_CODE = 'THIRD_PARTY'
--AND ZPTP.CUSTOMER_FLAG = 'Y'
AND ZPTP.PARTY_TAX_PROFILE_ID = HCA.OWNER_TABLE_ID
AND HCA.OWNER_TABLE_NAME = 'ZX_PARTY_TAX_PROFILE'
AND HCA.CLASS_CATEGORY = 'AR_GTA_TAXPAYER_TYPE'
AND SYSDATE BETWEEN HCA.START_DATE_ACTIVE AND NVL(HCA.END_DATE_ACTIVE,SYSDATE+1)
AND h.customer_trx_id = p_customer_trx_id;
SELECT z.DOCUMENT_SUB_TYPE
INTO l_document_subtype
FROM zx_lines_det_factors z
WHERE z.TRX_ID = p_customer_trx_id
AND z.TRX_LINE_ID = p_trx_line_id;
ar_gta_trx_headers_all_pkg.insert_row
(p_row_id => header_row_id
,p_ra_gl_date => p_gta_trx.trx_header.ra_gl_date
,p_ra_gl_period => p_gta_trx.trx_header.ra_gl_period
,p_set_of_books_id => p_gta_trx.trx_header.set_of_books_id
,p_bill_to_customer_id => p_gta_trx.trx_header.bill_to_customer_id
,p_bill_to_customer_number => p_gta_trx.trx_header.bill_to_customer_number
,p_bill_to_customer_name => p_gta_trx.trx_header.bill_to_customer_name
,p_source => p_gta_trx.trx_header.SOURCE
,p_org_id => p_gta_trx.trx_header.org_id
,p_rule_header_id => p_gta_trx.trx_header.rule_header_id
,p_gta_trx_header_id => p_gta_trx.trx_header.gta_trx_header_id
,p_gta_trx_number => p_gta_trx.trx_header.gta_trx_number
,p_group_number => p_gta_trx.trx_header.group_number
,p_version => p_gta_trx.trx_header.version
,p_latest_version_flag => p_gta_trx.trx_header.latest_version_flag
,p_transaction_date => p_gta_trx.trx_header.transaction_date
,p_ra_trx_id => p_gta_trx.trx_header.ra_trx_id
,p_ra_trx_number => p_gta_trx.trx_header.ra_trx_number
,p_description => p_gta_trx.trx_header.description
,p_customer_address => p_gta_trx.trx_header.customer_address
,p_customer_phone => p_gta_trx.trx_header.customer_phone
,p_customer_address_phone => p_gta_trx.trx_header.customer_address_phone
,p_bank_account_name => p_gta_trx.trx_header.bank_account_name
,p_bank_account_number => p_gta_trx.trx_header.bank_account_number
,p_bank_account_name_number => p_gta_trx.trx_header.bank_account_name_number
,p_fp_tax_registration_number => p_gta_trx.trx_header.fp_tax_registration_number -- fp registration number
,p_tp_tax_registration_number => p_gta_trx.trx_header.tp_tax_registration_number -- tp registration number
,p_legal_entity_id => p_gta_trx.trx_header.legal_entity_id -- legal entity id
,p_ra_currency_code => p_gta_trx.trx_header.ra_currency_code
,p_conversion_type => p_gta_trx.trx_header.conversion_type
,p_conversion_date => p_gta_trx.trx_header.conversion_date
,p_conversion_rate => p_gta_trx.trx_header.conversion_rate
,p_gta_batch_number => p_gta_trx.trx_header.gta_batch_number
,p_gt_invoice_number => p_gta_trx.trx_header.gt_invoice_number
,p_gt_invoice_date => p_gta_trx.trx_header.gt_invoice_date
,p_gt_invoice_net_amount => p_gta_trx.trx_header.gt_invoice_net_amount
,p_gt_invoice_tax_amount => p_gta_trx.trx_header.gt_invoice_tax_amount
,p_status => p_gta_trx.trx_header.status
,p_sales_list_flag => p_gta_trx.trx_header.sales_list_flag
,p_cancel_flag => p_gta_trx.trx_header.cancel_flag
,p_gt_invoice_type => p_gta_trx.trx_header.gt_invoice_type
,p_gt_invoice_class => p_gta_trx.trx_header.gt_invoice_class
,p_gt_tax_month => p_gta_trx.trx_header.gt_tax_month
,p_issuer_name => p_gta_trx.trx_header.issuer_name
,p_reviewer_name => p_gta_trx.trx_header.reviewer_name
,p_payee_name => p_gta_trx.trx_header.payee_name
,p_tax_code => p_gta_trx.trx_header.tax_code
,p_tax_rate => p_gta_trx.trx_header.tax_rate
,p_generator_id => p_gta_trx.trx_header.generator_id
,p_export_request_id => p_gta_trx.trx_header.export_request_id
,p_request_id => p_gta_trx.trx_header.request_id
,p_program_application_id => p_gta_trx.trx_header.program_application_id
,p_program_id => p_gta_trx.trx_header.program_id
,p_program_update_date => p_gta_trx.trx_header.program_update_date
,p_attribute_category => p_gta_trx.trx_header.attribute_category
,p_attribute1 => p_gta_trx.trx_header.attribute1
,p_attribute2 => p_gta_trx.trx_header.attribute2
,p_attribute3 => p_gta_trx.trx_header.attribute3
,p_attribute4 => p_gta_trx.trx_header.attribute4
,p_attribute5 => p_gta_trx.trx_header.attribute5
,p_attribute6 => p_gta_trx.trx_header.attribute6
,p_attribute7 => p_gta_trx.trx_header.attribute7
,p_attribute8 => p_gta_trx.trx_header.attribute8
,p_attribute9 => p_gta_trx.trx_header.attribute9
,p_attribute10 => p_gta_trx.trx_header.attribute10
,p_attribute11 => p_gta_trx.trx_header.attribute11
,p_attribute12 => p_gta_trx.trx_header.attribute12
,p_attribute13 => p_gta_trx.trx_header.attribute13
,p_attribute14 => p_gta_trx.trx_header.attribute14
,p_attribute15 => p_gta_trx.trx_header.attribute15
,p_creation_date => p_gta_trx.trx_header.creation_date
,p_created_by => p_gta_trx.trx_header.created_by
,p_last_update_date => p_gta_trx.trx_header.last_update_date
,p_last_updated_by => p_gta_trx.trx_header.last_updated_by
,p_last_update_login => p_gta_trx.trx_header.last_update_login
,p_invoice_type => p_gta_trx.trx_header.invoice_type
--Yao Zhang add begin for bug#8605196 ER3 consolidate invoice
,p_consolidation_flag => p_gta_trx.trx_header.consolidation_flag
,p_consolidation_id => p_gta_trx.trx_header.consolidation_id
,p_consolidation_trx_num => p_gta_trx.trx_header.consolidation_trx_num
--Yao Zhang add end for bug#8605196 ER3 consolidate invoice
);
ar_gta_trx_lines_all_pkg.insert_row
(p_rowid => line_row_id
,p_org_id => p_gta_trx.trx_lines(l_count).org_id
,p_gta_trx_header_id => p_gta_trx.trx_lines(l_count).gta_trx_header_id
,p_gta_trx_line_id => p_gta_trx.trx_lines(l_count).gta_trx_line_id
,p_matched_flag => p_gta_trx.trx_lines(l_count).matched_flag
,p_line_number => p_gta_trx.trx_lines(l_count).line_number
,p_ar_trx_line_id => p_gta_trx.trx_lines(l_count).ar_trx_line_id
,p_inventory_item_id => p_gta_trx.trx_lines(l_count).inventory_item_id
,p_item_number => p_gta_trx.trx_lines(l_count).item_number
,p_item_description => p_gta_trx.trx_lines(l_count).item_description
,p_item_model => p_gta_trx.trx_lines(l_count).item_model
,p_item_tax_denomination => p_gta_trx.trx_lines(l_count).item_tax_denomination
,p_tax_rate => p_gta_trx.trx_lines(l_count).tax_rate
,p_uom => p_gta_trx.trx_lines(l_count).uom
,p_uom_name => p_gta_trx.trx_lines(l_count).uom_name
,p_quantity => p_gta_trx.trx_lines(l_count).quantity
,p_price_flag => p_gta_trx.trx_lines(l_count).price_flag
,p_unit_price => p_gta_trx.trx_lines(l_count).unit_price
,p_unit_tax_price => p_gta_trx.trx_lines(l_count).unit_tax_price
,p_amount => p_gta_trx.trx_lines(l_count).amount
--modified by Jixun for bug#16027677 begin
,p_original_currency_amount => p_gta_trx.trx_lines(l_count).original_currency_amount
--,p_original_currency_amount => round(p_gta_trx.trx_lines(l_count).original_currency_amount/nvl(p_gta_trx.trx_header.conversion_rate,1),2)--added by shaoclbj for bug 12664154
--modified by Jixun for bug#16027677 end
,p_tax_amount => p_gta_trx.trx_lines(l_count).tax_amount
,p_discount_flag => p_gta_trx.trx_lines(l_count).discount_flag
,p_enabled_flag => p_gta_trx.trx_lines(l_count).enabled_flag
,p_request_id => p_gta_trx.trx_lines(l_count).request_id
,p_program_application_id => p_gta_trx.trx_lines(l_count).program_applicaton_id
,p_program_id => p_gta_trx.trx_lines(l_count).program_id
,p_program_update_date => p_gta_trx.trx_lines(l_count).program_update_date
,p_attribute_category => p_gta_trx.trx_lines(l_count).attribute_category
,p_attribute1 => p_gta_trx.trx_lines(l_count).attribute1
,p_attribute2 => p_gta_trx.trx_lines(l_count).attribute2
,p_attribute3 => p_gta_trx.trx_lines(l_count).attribute3
,p_attribute4 => p_gta_trx.trx_lines(l_count).attribute4
,p_attribute5 => p_gta_trx.trx_lines(l_count).attribute5
,p_attribute6 => p_gta_trx.trx_lines(l_count).attribute6
,p_attribute7 => p_gta_trx.trx_lines(l_count).attribute7
,p_attribute8 => p_gta_trx.trx_lines(l_count).attribute8
,p_attribute9 => p_gta_trx.trx_lines(l_count).attribute9
,p_attribute10 => p_gta_trx.trx_lines(l_count).attribute10
,p_attribute11 => p_gta_trx.trx_lines(l_count).attribute11
,p_attribute12 => p_gta_trx.trx_lines(l_count).attribute12
,p_attribute13 => p_gta_trx.trx_lines(l_count).attribute13
,p_attribute14 => p_gta_trx.trx_lines(l_count).attribute14
,p_attribute15 => p_gta_trx.trx_lines(l_count).attribute15
,p_creation_date => p_gta_trx.trx_lines(l_count).creation_date
,p_created_by => p_gta_trx.trx_lines(l_count).created_by
,p_last_update_date => p_gta_trx.trx_lines(l_count).last_update_date
,p_last_updated_by => p_gta_trx.trx_lines(l_count).last_updated_by
,p_last_update_login => p_gta_trx.trx_lines(l_count).last_update_login
--Yao Zhang add for bug#8605196 to support discount line
,p_discount_amount => p_gta_trx.trx_lines(l_count).discount_amount
,p_discount_tax_amount => p_gta_trx.trx_lines(l_count).discount_tax_amount
,p_discount_rate => p_gta_trx.trx_lines(l_count).discount_rate
);
,'Exception occur when insert data into database' ||
SQLCODE || SQLERRM);
log( 'Exception occur when insert data into database' ||SQLCODE || SQLERRM);
SELECT gta_trx_line_id
FROM ar_gta_trx_lines_all
WHERE gta_trx_header_id = l_header_id;
SELECT SUM(nvl(amount, 0) + nvl(tax_amount, 0) +
nvl(discount_amount, 0) + nvl(discount_tax_amount, 0))
INTO l_ret
FROM ar_gta_trx_lines_all
WHERE gta_trx_header_id = p_header_id
AND enabled_flag = 'Y';
SELECT
--SUM(nvl(amount,0))
SUM(nvl(amount, 0) + nvl(discount_amount, 0)) --Yao Modified for R12.1.2 to support discount line
INTO l_ret
FROM ar_gta_trx_lines_all
WHERE gta_trx_header_id = p_header_id
AND enabled_flag = 'Y';
SELECT
SUM(nvl(original_currency_amount,0))
FROM
ar_gta_trx_lines_all
WHERE gta_trx_header_id = p_header_id
AND enabled_flag = 'Y';
PROCEDURE delete_header_line_cascade
(p_gta_trx_header_id IN NUMBER)
IS
BEGIN
--Delete lines
DELETE ar_gta_trx_lines_all
WHERE gta_trx_header_id = p_gta_trx_header_id;
DELETE ar_gta_trx_headers_all
WHERE gta_trx_header_id = p_gta_trx_header_id;
END delete_header_line_cascade;
SELECT --SUM(nvl(tax_amount,0))
SUM(nvl(tax_amount,0)+nvl(discount_tax_amount,0))--Yao Modified for R12.1.2
INTO l_ret
FROM ar_gta_trx_lines
WHERE gta_trx_header_id = p_header_id
AND enabled_flag = 'Y';
SELECT
vat_tax_type_code
,gt_currency_code
FROM
ar_gta_system_parameters_all
WHERE org_id=p_org_id;
SELECT
COUNT(*)
FROM
zx_lines
WHERE trx_line_id=p_customer_trx_line_id
AND entity_code='TRANSACTIONS'
AND application_id = 222
AND trx_level_type='LINE'
AND tax_type_code=l_tax_type_code
AND tax_currency_code=l_gt_currency_code
AND event_class_code IN ('INVOICE','CREDIT_MEMO','DEBIT_MEMO')--jogen bug5212702 May-17,2006
AND trx_id=pc_trx_id; --jogen bug5212702 May-17,2006
SELECT customer_trx_id
INTO l_trx_id
FROM ra_customer_trx_lines_all
WHERE customer_trx_line_id=p_customer_trx_line_id;
SELECT
vat_tax_type_code
,gt_currency_code
FROM
ar_gta_system_parameters_all
WHERE org_id=p_org_id;
SELECT COUNT(*)
FROM
(SELECT
trx_line_id
,COUNT(*)
FROM
zx_lines
WHERE application_id = 222
AND trx_id=p_customer_trx_id
AND trx_level_type='LINE'
AND entity_code='TRANSACTIONS'
AND tax_type_code=l_tax_type_code
AND tax_currency_code=l_gt_currency_code
AND event_class_code IN ('INVOICE','CREDIT_MEMO','DEBIT_MEMO')--jogen bug5212702 May-17,2006
GROUP BY trx_line_id
HAVING COUNT(*)>1);
SELECT
vat_tax_type_code
,gt_currency_code
FROM
ar_gta_system_parameters_all
WHERE org_id=p_org_id;
SELECT
NVL(SUM(taxable_amt_tax_curr),0)
FROM
zx_lines
WHERE application_id = 222
AND trx_id=p_customer_trx_id
AND trx_level_type='LINE'
AND entity_code='TRANSACTIONS'
AND tax_type_code=l_tax_type_code
AND tax_currency_code=l_gt_currency_code
AND event_class_code IN ('INVOICE','CREDIT_MEMO','DEBIT_MEMO'); --Donghai Wang bug5212702 May-17,2006
SELECT
vat_tax_type_code
,gt_currency_code
FROM
ar_gta_system_parameters_all
WHERE org_id=p_org_id;
SELECT
NVL(SUM(tax_amt_tax_curr),0)
FROM
zx_lines
WHERE application_id = 222
AND trx_id=p_customer_trx_id
AND trx_level_type='LINE'
AND entity_code='TRANSACTIONS'
AND tax_type_code=l_tax_type_code
AND tax_currency_code=l_gt_currency_code
AND event_class_code IN ('INVOICE','CREDIT_MEMO','DEBIT_MEMO'); --Donghai Wang bug5212702 May-17,2006;
SELECT
hcp.phone_number
FROM
hz_contact_points hcp
WHERE hcp.contact_point_type = 'PHONE'
AND hcp.owner_table_name = 'HZ_PARTIES'
AND hcp.owner_table_id = (SELECT
party_id
FROM
hz_cust_accounts_all
WHERE cust_account_id=l_customer_id
)
AND hcp.primary_flag = 'Y';
SELECT OTL.NAME
FROM HR_ALL_ORGANIZATION_UNITS O
, HR_ALL_ORGANIZATION_UNITS_TL OTL
WHERE O.ORGANIZATION_ID = OTL.ORGANIZATION_ID
AND OTL.LANGUAGE = userenv('LANG')
AND O.ORGANIZATION_ID = p_org_id;
SELECT
p.party_name
FROM
hz_parties p
,hz_cust_accounts a
WHERE a.cust_account_id = p_customer_id
AND p.party_id = a.party_id;
SELECT
vat_tax_type_code
,gt_currency_code
FROM
ar_gta_system_parameters_all
WHERE org_id=p_org_id;
SELECT
taxable_amt_tax_curr
FROM
zx_lines
WHERE trx_line_id=p_customer_trx_line_id
AND entity_code='TRANSACTIONS'
AND application_id = 222
AND trx_level_type='LINE'
AND tax_type_code=l_tax_type_code
AND tax_currency_code=l_gt_currency_code
AND event_class_code IN ('INVOICE','CREDIT_MEMO','DEBIT_MEMO')--Donghai Wang bug5212702 May-17,2006
AND trx_id=pc_trx_id
ORDER BY tax_line_id;
SELECT customer_trx_id
INTO l_trx_id
FROM ra_customer_trx_lines_all
WHERE customer_trx_line_id=p_customer_trx_line_id;
SELECT
vat_tax_type_code
,gt_currency_code
FROM
ar_gta_system_parameters_all
WHERE org_id=p_org_id;
SELECT
tax_amt_tax_curr
FROM
zx_lines
WHERE trx_line_id=p_customer_trx_line_id
AND entity_code='TRANSACTIONS'
AND application_id = 222
AND trx_level_type='LINE'
AND tax_type_code=l_tax_type_code
AND tax_currency_code=l_gt_currency_code
AND event_class_code IN ('INVOICE','CREDIT_MEMO','DEBIT_MEMO')--Donghai Wang bug5212702 May-17,2006
AND trx_id=pc_trx_id
ORDER BY tax_line_id;
SELECT customer_trx_id
INTO l_trx_id
FROM ra_customer_trx_lines_all
WHERE customer_trx_line_id=p_customer_trx_line_id;
SELECT
vat_tax_type_code
,gt_currency_code
FROM
ar_gta_system_parameters_all
WHERE org_id=p_org_id;
SELECT
tax_rate
FROM
zx_lines
WHERE trx_line_id=p_customer_trx_line_id
AND entity_code='TRANSACTIONS'
AND application_id = 222
AND trx_level_type='LINE'
AND tax_type_code=l_tax_type_code
AND tax_currency_code=l_gt_currency_code
AND event_class_code IN ('INVOICE','CREDIT_MEMO','DEBIT_MEMO')--Donghai Wang bug5212702 May-17,2006
AND trx_id=pc_trx_id --Donghai Wang bug5212702 May-17,2006
ORDER BY tax_line_id;
SELECT customer_trx_id
INTO l_trx_id
FROM ra_customer_trx_lines_all
WHERE customer_trx_line_id=p_customer_trx_line_id;
SELECT
gt_currency_code
INTO
l_currency_code
FROM
ar_gta_system_parameters_all
WHERE org_id=p_org_id;
SELECT
h.paying_customer_id
,h.paying_site_use_id
,h.payment_trxn_extension_id
--Yao Zhang add begin for bug#8404856
,h.bill_to_customer_id
,h.bill_to_site_use_id
--Yao Zhang add end for bug#8404856
INTO
l_paying_customer_id
, l_paying_site_use_id
, l_trxn_extension_id
--Yao Zhang add for bug#8404856
, l_bill_to_customer_id
, l_bill_to_site_use_id
--Yao Zhang add end for bug#8404856
FROM
ra_customer_trx_all h
WHERE h.customer_trx_id = p_customer_trx_id ;
, 'no date found when select header info');
SELECT
u.instrument_id
, b.bank_account_name
--Modified by Yao begin for bug#8605196 to support Bank name in Chinese
--, b.bank_name
, decode(bhp.organization_name_phonetic
,null, bhp.party_name
,bhp.organization_name_phonetic)
--, b.bank_branch_name
, decode(brhp.organization_name_phonetic
,null, brhp.party_name
,brhp.organization_name_phonetic)
--Modified by Yao for bug#8605196 end to support Bank name in Chinese
INTO
l_instrument_id
, l_bank_account_name
, l_bank_name
, l_bank_branch_name
FROM IBY_CREDITCARD C,
IBY_CREDITCARD_ISSUERS_VL I,
IBY_EXT_BANK_ACCOUNTS_V B,
IBY_FNDCPT_PMT_CHNNLS_VL P,
IBY_FNDCPT_TX_EXTENSIONS X,
IBY_FNDCPT_TX_OPERATIONS OP,
IBY_PMT_INSTR_USES_ALL U,
HZ_PARTIES HZP,
FND_APPLICATION A,
--Add by Yao for bug#8605196 to support bank name in Chinese
HZ_PARTIES bhp,
HZ_PARTIES brhp
WHERE (x.instr_assignment_id = u.instrument_payment_use_id(+))
AND (DECODE(u.instrument_type, 'CREDITCARD', u.instrument_id, NULL) =
c.instrid(+))
AND (DECODE(u.instrument_type, 'BANKACCOUNT', u.instrument_id, NULL) =
b.bank_account_id(+))
AND (x.payment_channel_code = p.payment_channel_code)
AND (c.card_issuer_code = i.card_issuer_code(+))
AND (x.trxn_extension_id = op.trxn_extension_id(+))
AND (c.card_owner_id = hzp.party_id(+))
AND (x.origin_application_id = a.application_id)
AND x.trxn_extension_id = l_trxn_extension_id
--Add by Yao for bug#8605196 to support bank name in Chinese
AND b.bank_party_id=bhp.party_id(+)
AND b.branch_party_id=brhp.party_id(+);
, 'no date found when select bank information');
SELECT
bank_account_num
INTO
l_bank_account_num
FROM
IBY_EXT_BANK_ACCOUNTS
WHERE
ext_bank_account_id = l_instrument_id;
, 'no date found when select bank information');
SELECT
party_id
INTO
l_paying_party_id
FROM
HZ_CUST_ACCOUNTS
WHERE
CUST_ACCOUNT_ID = l_valid_customer_id ;--Yao Zhang modified for bug#8404856
SELECT
ext_payer_id
INTO
l_ext_payer_id
FROM
IBY_EXTERNAL_PAYERS_ALL
WHERE party_id = l_paying_party_id
AND CUST_ACCOUNT_ID = l_valid_customer_id--Yao Zhang modified for bug#8404856
AND ACCT_SITE_USE_ID =l_valid_site_use_id--Yao Zhang modified for bug#8404856
AND ORG_ID = p_org_id -- org id
AND org_type = 'OPERATING_UNIT' -- ou
AND payment_function = 'CUSTOMER_PAYMENT';
SELECT
bank_account_name
, bank_account_num
, bank_id
, branch_id
INTO
l_bank_account_name
, l_bank_account_num
, l_bank_id
, l_bank_branch_id
FROM (SELECT ibybanks.bank_account_name
, ibybanks.bank_account_num
, ibybanks.bank_id
, ibybanks.branch_id
FROM IBY_PMT_INSTR_USES_ALL ExtPartyInstrumentsEO
, IBY_EXT_BANK_ACCOUNTS ibybanks
WHERE ibybanks.EXT_BANK_ACCOUNT_ID = ExtPartyInstrumentsEO.instrument_id
AND ExtPartyInstrumentsEO.INSTRUMENT_TYPE = 'BANKACCOUNT'
AND ExtPartyInstrumentsEO.EXT_PMT_PARTY_ID = l_ext_payer_id
AND ExtPartyInstrumentsEO.PAYMENT_FUNCTION = 'CUSTOMER_PAYMENT'
AND (ibybanks.currency_code = l_currency_code OR ibybanks.currency_code IS NULL)
AND SYSDATE BETWEEN nvl(ExtPartyInstrumentsEO.START_DATE, to_date('1900-01-01','RRRR-MM-DD'))
AND nvl(ExtPartyInstrumentsEO.END_DATE, to_date('3000-01-01','RRRR-MM-DD'))
ORDER BY ibybanks.currency_code,ExtPartyInstrumentsEO.ORDER_OF_PREFERENCE)
WHERE ROWNUM =1;
SELECT
decode(organization_name_phonetic
,null, party_name
,organization_name_phonetic)
--Modified end by Yao for bug#8605196 to support bank name in Chinese
INTO
l_bank_name
FROM
HZ_PARTIES
WHERE
party_id = l_bank_id;
SELECT
--Modified begin by Yao for bug#8605196 to support bank name in Chinese
decode(organization_name_phonetic
,null, party_name
,organization_name_phonetic)
--Modified end by Yao for bug#8605196 to support bank name in Chinese
INTO
l_bank_branch_name
FROM
HZ_PARTIES
WHERE party_id = l_bank_branch_id;
, 'no date found when select bank information');
SELECT
gt_currency_code
INTO
l_currency_code
FROM
ar_gta_system_parameters_all
WHERE org_id=p_org_id;
SELECT
h.paying_customer_id
,h.paying_site_use_id
,h.payment_trxn_extension_id
--Yao Zhang add begin for bug#8404856
,h.bill_to_customer_id
,h.bill_to_site_use_id
--Yao Zhang add end for bug#8404856
INTO
l_paying_customer_id
, l_paying_site_use_id
, l_trxn_extension_id
--Yao Zhang add begin for bug#8404856
,l_bill_to_customer_id
,l_bill_to_site_use_id
--Yao Zhang add end for bug#8404856
FROM
ra_customer_trx_all h
WHERE h.customer_trx_id = p_customer_trx_id ;
, 'no date found when select bank information');
SELECT
u.instrument_id
, b.bank_account_name
--Modified by Yao begin for bug#8605196 to support Bank name in Chinese
--, b.bank_name
, decode(bhp.organization_name_phonetic
,null, bhp.party_name
,bhp.organization_name_phonetic)
--, b.bank_branch_name
, decode(brhp.organization_name_phonetic
,null, brhp.party_name
,brhp.organization_name_phonetic)
--Modified by Yao end for bug#8605196 to support Bank name in Chinese
INTO
l_instrument_id
, l_bank_account_name
, l_bank_name
, l_bank_branch_name
FROM IBY_CREDITCARD C,
IBY_CREDITCARD_ISSUERS_VL I,
IBY_EXT_BANK_ACCOUNTS_V B,
IBY_FNDCPT_PMT_CHNNLS_VL P,
IBY_FNDCPT_TX_EXTENSIONS X,
IBY_FNDCPT_TX_OPERATIONS OP,
IBY_PMT_INSTR_USES_ALL U,
HZ_PARTIES HZP,
FND_APPLICATION A,
--Add by Yao for bug#8605196 to support bank name in Chinese
HZ_PARTIES bhp,
HZ_PARTIES brhp
WHERE (x.instr_assignment_id = u.instrument_payment_use_id(+))
AND (DECODE(u.instrument_type, 'CREDITCARD', u.instrument_id, NULL) =
c.instrid(+))
AND (DECODE(u.instrument_type, 'BANKACCOUNT', u.instrument_id, NULL) =
b.bank_account_id(+))
AND (x.payment_channel_code = p.payment_channel_code)
AND (c.card_issuer_code = i.card_issuer_code(+))
AND (x.trxn_extension_id = op.trxn_extension_id(+))
AND (c.card_owner_id = hzp.party_id(+))
AND (x.origin_application_id = a.application_id)
AND x.trxn_extension_id = l_trxn_extension_id
--Add by Yao for bug#8605196 to support bank name in Chinese
AND b.bank_party_id=bhp.party_id(+)
AND b.branch_party_id=brhp.party_id(+);
, 'no date found when select bank information');
SELECT
bank_account_num
INTO
l_bank_account_num
FROM
IBY_EXT_BANK_ACCOUNTS
WHERE
ext_bank_account_id = l_instrument_id;
, 'no date found when select bank information');
SELECT
h.paying_customer_id
,h.paying_site_use_id
,h.payment_trxn_extension_id
INTO
l_ori_paying_customer_id
, l_ori_paying_site_use_id
, l_ori_trxn_extension_id
FROM
ra_customer_trx_all h
WHERE h.customer_trx_id = p_original_trx_id ;
, 'no date found when select header info');
SELECT
u.instrument_id
, b.bank_account_name
--Modified by Yao begin for bug#8605196 to support Bank name in Chinese
--, b.bank_name
, decode(bhp.organization_name_phonetic
,null, bhp.party_name
,bhp.organization_name_phonetic)
--, b.bank_branch_name
, decode(brhp.organization_name_phonetic
,null, brhp.party_name
,brhp.organization_name_phonetic)
--Modified by Yao end for bug#8605196 to support Bank name in Chinese
INTO
l_instrument_id
, l_bank_account_name
, l_bank_name
, l_bank_branch_name
FROM IBY_CREDITCARD C,
IBY_CREDITCARD_ISSUERS_VL I,
IBY_EXT_BANK_ACCOUNTS_V B,
IBY_FNDCPT_PMT_CHNNLS_VL P,
IBY_FNDCPT_TX_EXTENSIONS X,
IBY_FNDCPT_TX_OPERATIONS OP,
IBY_PMT_INSTR_USES_ALL U,
HZ_PARTIES HZP,
FND_APPLICATION A,
--Add by Yao for bug#8605196 to support bank name in Chinese
HZ_PARTIES bhp,
HZ_PARTIES brhp
WHERE (x.instr_assignment_id = u.instrument_payment_use_id(+))
AND (DECODE(u.instrument_type, 'CREDITCARD', u.instrument_id, NULL) =
c.instrid(+))
AND (DECODE(u.instrument_type, 'BANKACCOUNT', u.instrument_id, NULL) =
b.bank_account_id(+))
AND (x.payment_channel_code = p.payment_channel_code)
AND (c.card_issuer_code = i.card_issuer_code(+))
AND (x.trxn_extension_id = op.trxn_extension_id(+))
AND (c.card_owner_id = hzp.party_id(+))
AND (x.origin_application_id = a.application_id)
AND x.trxn_extension_id = l_ori_trxn_extension_id
--Add by Yao to for bug#8605196 support bank name in Chinese
AND b.bank_party_id=bhp.party_id(+)
AND b.branch_party_id=brhp.party_id(+);
, 'no date found when select bank information');
SELECT
bank_account_num
INTO
l_bank_account_num
FROM
IBY_EXT_BANK_ACCOUNTS
WHERE
ext_bank_account_id = l_instrument_id;
, 'no date found when select bank information');
SELECT
party_id
INTO
l_paying_party_id
FROM
HZ_CUST_ACCOUNTS
WHERE
CUST_ACCOUNT_ID = l_valid_customer_id ;--Yao Zhang modified for bug#8404856
SELECT
ext_payer_id
INTO
l_ext_payer_id
FROM
IBY_EXTERNAL_PAYERS_ALL
WHERE party_id = l_paying_party_id
AND CUST_ACCOUNT_ID = l_valid_customer_id--Yao Zhang modified for bug#8404856
AND ACCT_SITE_USE_ID = l_valid_site_use_id--Yao Zhang modified for bug#8404856
AND ORG_ID = p_org_id -- org id
AND org_type = 'OPERATING_UNIT' -- ou
AND payment_function = 'CUSTOMER_PAYMENT';
SELECT
bank_account_name
, bank_account_num
, bank_id
, branch_id
INTO
l_bank_account_name
, l_bank_account_num
, l_bank_id
, l_bank_branch_id
FROM (SELECT ibybanks.bank_account_name
, ibybanks.bank_account_num
, ibybanks.bank_id
, ibybanks.branch_id
FROM IBY_PMT_INSTR_USES_ALL ExtPartyInstrumentsEO
, IBY_EXT_BANK_ACCOUNTS ibybanks
WHERE ibybanks.EXT_BANK_ACCOUNT_ID = ExtPartyInstrumentsEO.instrument_id
AND ExtPartyInstrumentsEO.INSTRUMENT_TYPE = 'BANKACCOUNT'
AND ExtPartyInstrumentsEO.EXT_PMT_PARTY_ID = l_ext_payer_id
AND ExtPartyInstrumentsEO.PAYMENT_FUNCTION = 'CUSTOMER_PAYMENT'
AND (ibybanks.currency_code = l_currency_code OR ibybanks.currency_code IS NULL)
AND SYSDATE BETWEEN nvl(ExtPartyInstrumentsEO.START_DATE, to_date('1900-01-01','RRRR-MM-DD'))
AND nvl(ExtPartyInstrumentsEO.END_DATE, to_date('3000-01-01','RRRR-MM-DD'))
ORDER BY ibybanks.currency_code,ExtPartyInstrumentsEO.ORDER_OF_PREFERENCE)
WHERE ROWNUM =1;
SELECT
decode(organization_name_phonetic
,null, party_name
,organization_name_phonetic)
--Modified end by Yao for bug#8605196 to support bank name in Chinese
INTO
l_bank_name
FROM
HZ_PARTIES
WHERE
party_id = l_bank_id;
SELECT
--Modified begin by Yao for bug#8605196 to support bank name in Chinese
decode(organization_name_phonetic
,null, party_name
,organization_name_phonetic)
--Modified end by Yao for bug#8605196 to support bank name in Chinese
INTO
l_bank_branch_name
FROM
HZ_PARTIES
WHERE party_id = l_bank_branch_id;
, 'no date found when select bank information');
SELECT customer_trx_id
INTO l_trx_id
FROM ra_customer_trx_lines_all
WHERE customer_trx_line_id=p_trx_line_id;
SELECT
COUNT(*)
INTO
l_tax_line_count
FROM
zx_lines tax
WHERE tax.trx_line_id = p_trx_line_id
AND tax.entity_code = 'TRANSACTIONS'
AND application_id = 222
AND tax.trx_level_type = 'LINE'
AND tax.tax_currency_code = p_currency_code
AND tax.tax_type_code = p_tax_type_code
AND tax.event_class_code IN ('INVOICE','CREDIT_MEMO','DEBIT_MEMO')--jogen bug5212702 May-17,2006
AND tax.trx_id=l_trx_id; --jogen bug5212702 May-17,2006
SELECT
tax.tax_line_id
INTO
l_tax_line_id
FROM
zx_lines tax
WHERE tax.trx_line_id = p_trx_line_id
AND tax.application_id = 222
AND tax.trx_level_type = 'LINE'
AND tax.entity_code = 'TRANSACTIONS'
AND tax.tax_type_code = p_tax_type_code
AND tax.event_class_code IN ('INVOICE','CREDIT_MEMO','DEBIT_MEMO')--jogen bug5212702 May-17,2006
AND tax.trx_id=l_trx_id; --jogen bug5212702 May-17,2006
SELECT
gt_currency_code
INTO
l_currency_code
FROM
ar_gta_system_parameters_all
WHERE org_id=p_org_id;
SELECT
tax.tax_line_id
, tax.hq_estb_reg_number
--Qiong modified for bug 10311408 change taxable_amt_tax_curr to (current exchange rate)*unrounded_taxable_amt
,nvl(tax.tax_currency_conversion_rate,1)*tax.unrounded_taxable_amt
-- , tax.taxable_amt_tax_curr
, tax.tax_rate
, tax.tax_amt_tax_curr
, tax.unit_price
, tax.trx_line_quantity
, tax.taxable_amt
, tax.Tax_currency_conversion_rate
INTO
l_tax_line_id
, l_tax_registration_number
, l_taxable_amount
, l_tax_rate
, l_tax_amount
, l_unit_price
, l_trx_line_quantity
, l_amount
, l_tax_curr_conversion_rate
FROM
zx_lines tax
WHERE tax.trx_line_id = p_trx_line_id
AND tax.entity_code = 'TRANSACTIONS'
AND application_id = 222
AND tax.trx_level_type = 'LINE'
AND tax.tax_currency_code = l_currency_code
AND tax.tax_type_code = p_tax_type_code
--jogen bug5212702 May-17,2006
AND tax.event_class_code IN ('INVOICE','CREDIT_MEMO','DEBIT_MEMO')
AND tax.trx_id=p_trx_id; --jogen bug5212702 May-17,2006
SELECT
COUNT(*)
INTO
l_fp_reg_number_count
FROM
ar_gta_tax_limits_all
WHERE org_id = p_org_id
AND fp_tax_registration_number = l_tax_registration_number;
SELECT
reg.registration_number
,reg.tax_regime_code
,reg.tax
,reg.tax_jurisdiction_code
INTO
l_tax_registration_number
,l_reg_tax_regime_code
,l_reg_tax
,l_reg_tax_jursidiction_code
FROM
zx_registrations reg
WHERE reg.party_tax_profile_id =p_party_tax_profile_id
AND (reg.tax is NULL or reg.tax = p_tax)
AND reg.tax_regime_code = p_tax_regime_code -- tax_regime_code is not null
AND (reg.tax_jurisdiction_code is NULL or reg.tax_jurisdiction_code = p_tax_jurisdiction_code)
AND p_tax_determine_date >= reg.effective_from
AND (p_tax_determine_date < reg.effective_to OR reg.effective_to IS NULL)
AND reg.registration_number IS NOT NULL;
SELECT
bill_to_site_use_id
INTO
l_bill_to_site_use_id
FROM
ra_customer_trx_all trx_header
WHERE trx_header.customer_trx_id = p_trx_id;
SELECT
cust_acct_site_id
INTO
l_cust_acct_site_id
FROM
hz_cust_site_uses_all
WHERE SITE_USE_ID = l_bill_to_site_use_id;
SELECT
party_site_id
INTO
l_party_site_id
FROM
hz_cust_acct_sites_all
WHERE cust_acct_site_id = l_cust_acct_site_id;
SELECT
tax.tax_regime_code
, tax.tax
, tax.tax_jurisdiction_code
, tax.tax_determine_date
INTO
l_tax_regime_code
, l_tax
, l_tax_jurisdiction_code
, l_tax_determine_date
FROM
zx_lines tax
WHERE
tax.tax_line_id = p_tax_line_id;
SELECT
party_tax_profile_id
INTO
l_party_tax_profile_id
FROM
zx_party_tax_profile tax_prof
WHERE tax_prof.party_id = l_party_site_id
AND tax_prof.party_type_code = 'THIRD_PARTY_SITE';
FND_MSG_PUB.Delete_Msg(l_indexO);
SELECT party_id
INTO l_party_id
FROM HZ_CUST_ACCOUNTS acct, hz_cust_acct_sites_all acct_site
WHERE acct.CUST_ACCount_ID = acct_site.CUST_ACCount_ID
AND acct_site.party_site_id = l_party_site_id;
SELECT reg.registration_number,
reg.tax_regime_code,
reg.tax,
reg.tax_jurisdiction_code
INTO l_tax_registration_number,
l_reg_tax_regime_code,
l_reg_tax,
l_reg_tax_jursidiction_code
FROM zx_registrations reg, zx_party_tax_profile tax_prof
WHERE reg.party_tax_profile_id = tax_prof.party_tax_profile_id
AND (reg.tax IS NULL OR reg.tax = l_tax)
AND reg.tax_regime_code = l_tax_regime_code -- tax_regime_code is not null
AND (reg.tax_jurisdiction_code IS NULL OR
reg.tax_jurisdiction_code = l_tax_jurisdiction_code)
AND l_tax_determine_date >= reg.effective_from
AND (l_tax_determine_date < reg.effective_to OR
reg.effective_to IS NULL)
AND reg.registration_number IS NOT NULL
AND tax_prof.party_type_code = 'THIRD_PARTY'
AND tax_prof.party_id = l_party_id
AND rownum = 1
ORDER BY reg.tax, reg.tax_jurisdiction_code;
FND_MSG_PUB.Delete_Msg(l_indexO);
SELECT
vat_tax_type_code
,gt_currency_code
FROM
ar_gta_system_parameters_all
WHERE org_id=p_org_id;
SELECT
tax_line_id
FROM
zx_lines
WHERE trx_line_id=p_customer_trx_line_id
AND entity_code='TRANSACTIONS'
AND application_id = 222
AND trx_id = p_customer_trx_id
AND trx_level_type='LINE'
AND tax_type_code=l_tax_type_code
AND tax_currency_code=l_gt_currency_code
AND event_class_code IN ('INVOICE','CREDIT_MEMO','DEBIT_MEMO')--Donghai Wang bug5212702 May-17,2006
AND trx_id=pc_trx_id --Donghai Wang bug5212702 May-17,2006
ORDER BY tax_line_id;
SELECT customer_trx_id
INTO l_trx_id
FROM ra_customer_trx_lines_all
WHERE customer_trx_line_id=p_customer_trx_line_id;
SELECT RA_BATCH_SOURCES_all.NAME
FROM RA_BATCH_SOURCES_all
WHERE org_id = p_org_id
AND BATCH_SOURCE_ID = p_source_id;
SELECT TO_CHAR(p_date, 'YYYY-MM-DD')
INTO l_xsd_date_string
FROM DUAL;
SELECT
gt_currency_code
FROM
ar_gta_system_parameters_all
WHERE
org_id=p_org_id;
UPDATE ar_gta_tax_limits_all
SET invoice_type=0
WHERE invoice_type IS NULL
AND org_id = p_org_id;
UPDATE ar_gta_rule_headers_all
SET invoice_type=0
WHERE invoice_type IS NULL
AND org_id = p_org_id;
SELECT JGTH.GTA_TRX_NUMBER, SOURCE
FROM RA_CUSTOMER_TRX_ALL RCT
, AR_GTA_TRX_HEADERS_ALL JGTH
WHERE invoice_type is null
AND JGTH.ORG_ID = p_org_id
AND RCT.CUSTOMER_TRX_ID(+) = JGTH.Ra_Trx_Id
AND NOT EXISTS (SELECT JGTL.Limitation_Id
FROM ar_gta_tax_limits_all JGTL
,ar_gta_type_mappings JGTM
WHERE JGTL.ORG_ID = JGTH.Org_Id
AND JGTL.FP_TAX_REGISTRATION_NUMBER = JGTH.FP_TAX_REGISTRATION_NUMBER
AND JGTM.Limitation_Id = JGTL.LIMITATION_ID
AND JGTM.TRANSACTION_TYPE_ID = RCT.CUST_TRX_TYPE_ID);
SELECT JGTH.GTA_TRX_NUMBER, SOURCE
FROM RA_CUSTOMER_TRX_ALL RCT
, AR_GTA_TRX_HEADERS_ALL JGTH
WHERE invoice_type is null
AND JGTH.ORG_ID = p_org_id
AND RCT.CUSTOMER_TRX_ID(+) = JGTH.Ra_Trx_Id
AND EXISTS (SELECT JGTL.Limitation_Id
FROM ar_gta_tax_limits_all JGTL
,ar_gta_type_mappings JGTM
WHERE JGTL.ORG_ID = JGTH.Org_Id
AND JGTL.FP_TAX_REGISTRATION_NUMBER = JGTH.FP_TAX_REGISTRATION_NUMBER
AND JGTM.Limitation_Id = JGTL.LIMITATION_ID
AND JGTM.TRANSACTION_TYPE_ID = RCT.CUST_TRX_TYPE_ID);
SELECT JGTH.GTA_TRX_NUMBER, SOURCE
FROM RA_CUSTOMER_TRX_ALL RCT,
RA_CUST_TRX_TYPES_ALL RCTT,
AR_GTA_TRX_HEADERS_ALL JGTH
WHERE invoice_type is not null
AND JGTH.ORG_ID = p_org_id
AND RCT.CUSTOMER_TRX_ID(+) = JGTH.Ra_Trx_Id
AND RCT.CUST_TRX_TYPE_ID = RCTT.CUST_TRX_TYPE_ID(+)
AND RCTT.ORG_ID=JGTH.ORG_ID
AND RCTT.TYPE = 'CM'
AND RCT.previous_customer_trx_id is not null
AND JGTH.invoice_type <>
(SELECT DISTINCT invoice_type
FROM AR_GTA_TRX_HEADERS_ALL JGTH1
WHERE JGTH1.RA_TRX_id = RCT.previous_customer_trx_id);
CURSOR c_all_inv_updated
IS
SELECT GTA_TRX_HEADER_ID
, GTA_TRX_NUMBER
, SOURCE
, RA_TRX_ID
, FP_TAX_REGISTRATION_NUMBER
, ORG_ID
FROM AR_GTA_TRX_HEADERS_ALL
WHERE INVOICE_TYPE IS NULL
AND ORG_ID = p_org_id;
OPEN c_all_inv_updated;
FETCH c_all_inv_updated
INTO l_gta_trx_header_id
, l_gta_trx_number
, l_source
, l_ra_trx_id
, l_fp_tax_registration_number
, l_org_id;
EXIT WHEN c_all_inv_updated%NOTFOUND;
SELECT JGTL.invoice_type
INTO l_invoice_type
FROM RA_CUSTOMER_TRX_ALL RCT
,ar_gta_tax_limits_all JGTL
WHERE RCT.CUSTOMER_TRX_ID = l_ra_trx_id
AND JGTL.ORG_ID = l_org_id
AND JGTL.FP_TAX_REGISTRATION_NUMBER = l_fp_tax_registration_number
AND RCT.CUST_TRX_TYPE_ID in
(SELECT JGTM.TRANSACTION_TYPE_ID
FROM ar_gta_type_mappings JGTM
WHERE JGTM.Limitation_Id = JGTL.LIMITATION_ID)
AND (JGTL.invoice_type IN ('0', '2') OR
(JGTL.invoice_type = '1' AND NOT EXISTS
( SELECT *
FROM ar_gta_trx_lines_all JGTLA
WHERE JGTLA.GTA_TRX_HEADER_ID = l_gta_trx_header_id
AND JGTLA.Org_Id = l_org_id
AND (JGTLA.Tax_Rate <> 0 OR
JGTLA.Tax_Amount <> 0))));
SELECT RCTT.TYPE
INTO l_ar_trx_type
FROM RA_CUST_TRX_TYPES_ALL RCTT
,RA_CUSTOMER_TRX_ALL RCT
,AR_GTA_TRX_HEADERS_ALL JGTH
WHERE JGTH.GTA_TRX_HEADER_ID = l_gta_trx_header_id
AND RCT.CUSTOMER_TRX_ID(+) = JGTH.Ra_Trx_Id
AND RCT.CUST_TRX_TYPE_ID = RCTT.CUST_TRX_TYPE_ID(+)
AND RCTT.ORG_ID = l_org_id;
SELECT DISTINCT JGTH.invoice_type
INTO l_pre_trx_invoice_type
FROM AR_GTA_TRX_HEADERS_ALL JGTH
,RA_CUSTOMER_TRX_ALL RCT
WHERE RCT.CUSTOMER_TRX_ID(+) = l_ra_trx_id
AND JGTH.RA_TRX_id = RCT.previous_customer_trx_id;
END LOOP; -- c_all_inv_updated%NOTFOUND;
CLOSE c_all_inv_updated;
UPDATE AR_GTA_TRX_HEADERS_ALL JGTH
SET invoice_type = (SELECT JGTL.invoice_type
FROM RA_CUSTOMER_TRX_ALL RCT,
ar_gta_tax_limits_all JGTL
WHERE RCT.CUSTOMER_TRX_ID = JGTH.Ra_Trx_Id
AND JGTL.ORG_ID = JGTH.Org_Id
AND JGTL.FP_TAX_REGISTRATION_NUMBER =
JGTH.FP_TAX_REGISTRATION_NUMBER
AND RCT.CUST_TRX_TYPE_ID in
(SELECT JGTM.TRANSACTION_TYPE_ID
FROM ar_gta_type_mappings JGTM
WHERE JGTM.Limitation_Id = JGTL.LIMITATION_ID)
AND (JGTL.invoice_type IN ('0', '2') OR
(JGTL.invoice_type = '1' AND NOT EXISTS
( SELECT *
FROM ar_gta_trx_lines_all JGTLA
WHERE JGTLA.GTA_TRX_HEADER_ID =
JGTH.GTA_TRX_HEADER_ID
AND JGTH.Org_Id = JGTLA.Org_Id
AND (JGTLA.Tax_Rate <> 0 OR
JGTLA.Tax_Amount <> 0)))))
WHERE invoice_type IS NULL
AND JGTH.ORG_ID = p_org_id;