[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Data will be inserted into GTs before extract view call
*/
CURSOR l_doc_rec_csr
(c_extension_id iby_trxn_summaries_all.initiator_extension_id%TYPE,
c_card_data_level iby_trxn_core.card_data_level%TYPE) IS
/*
SELECT
XMLConcat(XMLAgg(document_headers),
XMLElement("DocumentReceivableCount",count(trxn_extension_id)))
FROM iby_xml_fndcpt_doc_headers_v
WHERE trxn_extension_id = c_extension_id;
SELECT
XMLAgg(
XMLConcat(XMLElement("DocumentReceivable",
XMLElement("DocumentID", NVL(headergt.calling_app_doc_ref_number, headergt.doc_unique_ref)),
XMLElement("DocumentStatus",
XMLElement("Code", NULL),
XMLElement("Meaning", NULL)),
XMLElement("DocumentDate", to_char(headergt.document_date, 'YYYY-MM-DD"T"HH24:MI:SS')),
XMLElement("DocumentCreationDate", to_char(headergt.creation_date, 'YYYY-MM-DD"T"HH24:MI:SS')),
XMLForest(NULL AS "PaymentDueDate"),
XMLElement("DocumentType",
XMLElement("Code", headergt.document_type),
XMLElement("Meaning", NULL)),
XMLElement("DocumentDescription", headergt.document_description),
XMLElement("BRSignedFlag", headergt.br_signed_flag),
XMLElement("BRDraweeIssuedFlag", headergt.br_drawee_issued_flag),
XMLElement("TotalDocumentAmount",
XMLElement("Value", headergt.document_amount),
XMLElement("Currency",
XMLElement("Code", headergt.document_currency_code))),
XMLElement("PaymentAmount",
XMLElement("Value", headergt.settlement_amount),
XMLElement("Currency",
XMLElement("Code", headergt.document_currency_code))),
XMLElement("Charge",
XMLElement("Amount",
XMLElement("Value", nvl(headergt.freight_amount, 0)),
XMLElement("Currency",
XMLElement("Code", headergt.document_currency_code))),
XMLForest('FREIGHT' AS "ChargeType")),
XMLElement("Discount",
XMLElement("Amount",
XMLElement("Value", headergt.document_discount_earned),
XMLElement("Currency",
XMLElement("Code", headergt.document_currency_code))),
XMLForest(NULL AS "DiscountType")),
XMLElement("Tax",
XMLElement("Amount",
XMLElement("Value", nvl(headergt.local_tax_amount, 0) + nvl(headergt.national_tax_amount, 0)),
XMLElement("Currency",
XMLElement("Code", headergt.document_currency_code))),
XMLElement("RatePercent", NULL),
XMLForest('SALESTAX' AS "TaxType"),
XMLForest(NULL AS "TaxJurisdiction")),
XMLElement("Tax",
XMLElement("Amount",
XMLElement("Value", headergt.vat_tax_amount),
XMLElement("Currency",
XMLElement("Code", headergt.document_currency_code))),
XMLElement("RatePercent", NULL),
XMLForest('VAT' AS "TaxType"),
XMLForest(NULL AS "TaxJurisdiction")),
XMLElement("ShipmentOrigin",
XMLElement("AddressLine1", headergt.address1),
XMLForest(headergt.address2 AS "AddressLine2"),
XMLForest(headergt.address3 AS "AddressLine3"),
XMLElement("City", headergt.city),
XMLForest(headergt.county AS "County"),
XMLElement("State", headergt.state),
XMLElement("Country", headergt.country),
XMLElement("PostalCode", headergt.postal_code)),
XMLElement("ShipmentDestination",
XMLElement("AddressLine1", headergt.to_address1),
XMLForest(headergt.to_address2 AS "AddressLine2"),
XMLForest(headergt.to_address3 AS "AddressLine3"),
XMLElement("City", headergt.to_city),
XMLForest(headergt.to_county AS "County"),
XMLElement("State", headergt.to_state),
XMLElement("Country", headergt.to_country),
XMLElement("PostalCode", headergt.to_postal_code)),
CASE
WHEN(c_card_data_level = '3') THEN
(SELECT xmlagg(
xmlelement("DocumentLine",
xmlelement("LineID", linegt.line_number),
xmlelement("LineNumber", linegt.line_number),
xmlforest(linegt.po_number AS "PONumber"),
xmlelement("LineType",
xmlelement("Code", linegt.line_type),
xmlelement("Meaning", NULL)),
xmlelement("LineDescription", linegt.description),
xmlelement("LineAmount",
xmlelement("Value", linegt.extended_amount),
xmlelement("Currency",
xmlelement("Code", linegt.invoice_currency_code))),
xmlelement("UnitRate", linegt.unit_price),
xmlelement("Quantity", linegt.quantity),
xmlelement("UnitOfMeasure", linegt.unit_of_measure),
xmlforest(linegt.inventory_item_id AS "ProductCode"),
xmlforest(NULL AS "CommodityCode"),
xmlelement("Discount",
xmlelement("Amount",
xmlelement("Value", linegt.discount_amount),
xmlelement("Currency",
xmlelement("Code", linegt.invoice_currency_code))),
xmlforest(NULL AS "DiscountType")),
xmlelement("Tax",
xmlelement("Amount",
xmlelement("Value", linegt.sales_tax_amount),
xmlelement("Currency",
xmlelement("Code", linegt.invoice_currency_code))),
xmlelement("RatePercent", linegt.tax_rate),
xmlforest('SALESTAX' AS "TaxType"),
xmlforest(NULL AS "TaxJurisdiction")),
xmlelement("Tax",
xmlelement("Amount",
xmlelement("Value", linegt.vat_tax_amount),
xmlelement("Currency",
xmlelement("Code", linegt.invoice_currency_code))),
xmlelement("RatePercent", linegt.tax_rate),
xmlforest('VAT' AS "TaxType"),
xmlforest(NULL AS "TaxJurisdiction"))))
FROM iby_ar_doc_lines_gt linegt
WHERE linegt.doc_unique_ref = headergt.doc_unique_ref)
ELSE
NULL
END
)))
FROM iby_ar_doc_header_gt headergt
WHERE headergt.initiator_extension_id = c_extension_id;
Data will be inserted into GTs before extract view call
*/
CURSOR l_doc_rec_csr
(c_extension_id iby_trxn_summaries_all.initiator_extension_id%TYPE,
c_card_data_level iby_trxn_core.card_data_level%TYPE) IS
/*
SELECT
XMLConcat(XMLAgg(document_headers),
XMLElement("DocumentReceivableCount",count(trxn_extension_id)))
FROM iby_xml_fndcpt_doc_headers_v
WHERE trxn_extension_id = c_extension_id;
SELECT
XMLAgg(
XMLConcat(XMLElement("DocumentReceivable",
XMLElement("DocumentID", NVL(headergt.calling_app_doc_ref_number, headergt.doc_unique_ref)),
XMLElement("DocumentStatus",
XMLElement("Code", NULL),
XMLElement("Meaning", NULL)),
XMLElement("DocumentDate", to_char(headergt.document_date, 'YYYY-MM-DD"T"HH24:MI:SS')),
XMLElement("DocumentCreationDate", to_char(headergt.creation_date, 'YYYY-MM-DD"T"HH24:MI:SS')),
XMLForest(NULL AS "PaymentDueDate"),
XMLElement("DocumentType",
XMLElement("Code", headergt.document_type),
XMLElement("Meaning", NULL)),
XMLElement("DocumentDescription", headergt.document_description),
XMLElement("BRSignedFlag", headergt.br_signed_flag),
XMLElement("BRDraweeIssuedFlag", headergt.br_drawee_issued_flag),
XMLElement("TotalDocumentAmount",
XMLElement("Value", headergt.document_amount),
XMLElement("Currency",
XMLElement("Code", headergt.document_currency_code))),
XMLElement("PaymentAmount",
XMLElement("Value", headergt.settlement_amount),
XMLElement("Currency",
XMLElement("Code", headergt.document_currency_code))),
XMLElement("Charge",
XMLElement("Amount",
XMLElement("Value", nvl(headergt.freight_amount, 0)),
XMLElement("Currency",
XMLElement("Code", headergt.document_currency_code))),
XMLForest('FREIGHT' AS "ChargeType")),
XMLElement("Discount",
XMLElement("Amount",
XMLElement("Value", headergt.document_discount_earned),
XMLElement("Currency",
XMLElement("Code", headergt.document_currency_code))),
XMLForest(NULL AS "DiscountType")),
XMLElement("Tax",
XMLElement("Amount",
XMLElement("Value", nvl(headergt.local_tax_amount, 0) + nvl(headergt.national_tax_amount, 0)),
XMLElement("Currency",
XMLElement("Code", headergt.document_currency_code))),
XMLElement("RatePercent", NULL),
XMLForest('SALESTAX' AS "TaxType"),
XMLForest(NULL AS "TaxJurisdiction")),
XMLElement("Tax",
XMLElement("Amount",
XMLElement("Value", headergt.vat_tax_amount),
XMLElement("Currency",
XMLElement("Code", headergt.document_currency_code))),
XMLElement("RatePercent", NULL),
XMLForest('VAT' AS "TaxType"),
XMLForest(NULL AS "TaxJurisdiction")),
XMLElement("ShipmentOrigin",
XMLElement("AddressLine1", headergt.address1),
XMLForest(headergt.address2 AS "AddressLine2"),
XMLForest(headergt.address3 AS "AddressLine3"),
XMLElement("City", headergt.city),
XMLForest(headergt.county AS "County"),
XMLElement("State", headergt.state),
XMLElement("Country", headergt.country),
XMLElement("PostalCode", headergt.postal_code)),
XMLElement("ShipmentDestination",
XMLElement("AddressLine1", headergt.to_address1),
XMLForest(headergt.to_address2 AS "AddressLine2"),
XMLForest(headergt.to_address3 AS "AddressLine3"),
XMLElement("City", headergt.to_city),
XMLForest(headergt.to_county AS "County"),
XMLElement("State", headergt.to_state),
XMLElement("Country", headergt.to_country),
XMLElement("PostalCode", headergt.to_postal_code)),
CASE
WHEN(c_card_data_level = '3') THEN
(SELECT xmlagg(
xmlelement("DocumentLine",
xmlelement("LineID", linegt.line_number),
xmlelement("LineNumber", linegt.line_number),
xmlforest(linegt.po_number AS "PONumber"),
xmlelement("LineType",
xmlelement("Code", linegt.line_type),
xmlelement("Meaning", NULL)),
xmlelement("LineDescription", linegt.description),
xmlelement("LineAmount",
xmlelement("Value", linegt.extended_amount),
xmlelement("Currency",
xmlelement("Code", linegt.invoice_currency_code))),
xmlelement("UnitRate", linegt.unit_price),
xmlelement("Quantity", linegt.quantity),
xmlelement("UnitOfMeasure", linegt.unit_of_measure),
xmlforest(linegt.inventory_item_id AS "ProductCode"),
xmlforest(NULL AS "CommodityCode"),
xmlelement("Discount",
xmlelement("Amount",
xmlelement("Value", linegt.discount_amount),
xmlelement("Currency",
xmlelement("Code", linegt.invoice_currency_code))),
xmlforest(NULL AS "DiscountType")),
xmlelement("Tax",
xmlelement("Amount",
xmlelement("Value", linegt.sales_tax_amount),
xmlelement("Currency",
xmlelement("Code", linegt.invoice_currency_code))),
xmlelement("RatePercent", linegt.tax_rate),
xmlforest('SALESTAX' AS "TaxType"),
xmlforest(NULL AS "TaxJurisdiction")),
xmlelement("Tax",
xmlelement("Amount",
xmlelement("Value", linegt.vat_tax_amount),
xmlelement("Currency",
xmlelement("Code", linegt.invoice_currency_code))),
xmlelement("RatePercent", linegt.tax_rate),
xmlforest('VAT' AS "TaxType"),
xmlforest(NULL AS "TaxJurisdiction"))))
FROM iby_ar_doc_lines_gt linegt
WHERE linegt.doc_unique_ref = headergt.doc_unique_ref)
ELSE
NULL
END
)))
FROM iby_ar_doc_header_gt headergt
WHERE headergt.initiator_extension_id = c_extension_id;
SELECT sp.exclude_trxn_det_extraction
INTO l_exclude_flag
FROM IBY_FNDCPT_USER_CC_PF_B up
,IBY_FNDCPT_SYS_CC_PF_B sp
WHERE up.user_cc_profile_code = p_process_profile
AND up.sys_cc_profile_code = sp.sys_cc_profile_code;
SELECT sp.exclude_trxn_det_extraction
INTO l_exclude_flag
FROM IBY_FNDCPT_USER_EFT_PF_B up
,IBY_FNDCPT_SYS_EFT_PF_B sp
WHERE up.user_eft_profile_code = p_process_profile
AND up.sys_eft_profile_code = sp.sys_eft_profile_code;
SELECT sp.exclude_trxn_det_extraction
INTO l_exclude_flag
FROM IBY_FNDCPT_USER_DC_PF_B up
,IBY_FNDCPT_SYS_DC_PF_B sp
WHERE up.user_dc_profile_code = p_process_profile
AND up.sys_dc_profile_code = sp.sys_dc_profile_code;
SELECT extended_amount
FROM ar_invoice_lines_v
WHERE (link_to_cust_trx_line_id IS NULL)
AND (line_type = 'FREIGHT')
AND (customer_trx_id = ci_cust_trx_id);
SELECT SUM(extended_amount)
FROM ar_invoice_lines_v
WHERE
(line_type = 'FREIGHT')
AND (customer_trx_id = ci_cust_trx_id);
SELECT customer_trx_line_id
FROM ar_invoice_lines_v
WHERE
( line_type = 'LINE')
AND (customer_trx_id = ci_cust_trx_id);
SELECT extended_amount
FROM ar_invoice_lines_v
WHERE
( line_type = 'LINE')
AND (customer_trx_id = ci_cust_trx_id);
SELECT extended_amount, location_rate_id, location_segment_id
FROM ar_invoice_lines_v
WHERE
(line_type='TAX')
AND (customer_trx_id = ci_cust_trx_id)
AND (link_to_cust_trx_line_id = ci_cust_trx_line_id);
SELECT location_rate_id, location_segment_id, tax_rate
FROM ar_invoice_lines_v
WHERE
(line_type='TAX')
AND (customer_trx_id = ci_cust_trx_id)
AND (link_to_cust_trx_line_id = ci_cust_trx_line_id);
SELECT tx.trxn_ref_number2
FROM iby_fndcpt_tx_extensions tx
WHERE
tx.trxn_extension_id = ci_trxn_ext_id
AND tx.trxn_ref_number1 = 'RECEIPT';
SELECT decode(summ.status, NULL, 'N', 'Y') AUTHORIZED_FLAG
FROM iby_trxn_summaries_all summ
, iby_fndcpt_tx_operations op
WHERE summ.transactionid = op.transactionid
AND reqtype = 'ORAPMTREQ'
AND status IN(0, 100)
AND op.trxn_extension_id = c_extension_id
AND ((trxntypeid IN(2, 3))
OR
(trxntypeid = 20
AND summ.trxnmid = (SELECT MAX(trxnmid)
FROM iby_trxn_summaries_all summ1,
iby_fndcpt_tx_operations op1
WHERE summ1.transactionid = op1.transactionid
AND summ1.reqtype = 'ORAPMTREQ'
AND summ1.status IN(0, 100)
AND summ1.trxntypeid IN(2, 3, 20)
AND op1.trxn_extension_id = op.trxn_extension_id
)
)
) ORDER BY op.transactionid ASC;