[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
jgth.gta_trx_header_id
,jgth.SOURCE
,rct.customer_trx_id
FROM
ar_gta_trx_headers_all jgth
,ra_customer_trx_all rct
WHERE jgth.org_id = l_org_id
AND jgth.status IN ('FAILED', 'COMPLETED', 'CANCELLED')
AND jgth.bill_to_customer_name LIKE nvl(p_customer_name,'%')
AND jgth.ra_gl_date >= l_gl_date_from
AND jgth.ra_gl_date <= l_gl_date_to
AND jgth.ra_trx_id = rct.customer_trx_id(+)
FOR UPDATE;
SELECT xmlelement("Parameters"
,xmlforest(ar_gta_trx_util.get_operatingunit(l_org_id) AS
"OperationUnit"
,p_customer_name AS
"ARCustomerName"
,ar_gta_trx_util.To_Xsd_Date_String(l_gl_date_from) AS
"ARTrxGLDateFrom"
,ar_gta_trx_util.To_Xsd_Date_String(l_gl_date_to) AS
"ARTrxGLDateTo"))
INTO l_parameter
FROM dual;
SELECT xmlelement("PurgeReport"
,xmlconcat(xmlelement("ReportFailed"
,'Y')
,xmlelement("FailedWithParameters"
,'N')
,xmlelement("RepDate"
,ar_gta_trx_util.To_Xsd_Date_String(SYSDATE))
,xmlelement("ReportFailedMsg"
,l_ar_gta_gta_not_enabled)
,l_parameter))
INTO l_report
FROM dual;
SELECT
COUNT(*)
INTO
l_line_count
FROM
ar_gta_trx_lines_all
WHERE gta_trx_header_id = l_gta_trx_header_id;
ar_gta_trx_util.delete_header_line_cascade(p_gta_trx_header_id => l_gta_trx_header_id);
IF l_source = 'AR' --count deleted GTA transaction headers and lines
THEN
l_gta_header_count := l_gta_header_count + 1;
ELSIF l_source = 'GT' --count deleted GT transaction headers and lines
THEN
l_gt_header_count := l_gt_header_count + 1;
SELECT xmlelement("Summary"
,xmlforest(l_gta_header_count AS "GTATrxHeaderPurged"
,l_gta_line_count AS "GTATrxLinePurged"
,l_gt_header_count AS "GTTrxHeaderPurged"
,l_gt_line_count AS "GTTrxLinePurged"))
INTO l_summary
FROM dual;
SELECT xmlelement("PurgeReport"
,xmlconcat(xmlelement("ReportFailed"
,'N')
,xmlelement("FailedWithParameters"
,'N')
,xmlelement("RepDate"
,ar_gta_trx_util.To_Xsd_Date_String(SYSDATE))
,l_parameter
,l_summary))
INTO l_report
FROM dual;
SELECT xmlelement("Parameters"
,xmlforest(ar_gta_trx_util.get_operatingunit(l_org_id)
AS "OperationUnit"
,p_fp_tax_reg_num
AS "TaxRegistrationNumber"
,ar_gta_trx_util.Get_AR_Batch_Source_Name
( l_org_id
, p_trx_source)
AS "TransactionSource"
,ar_gta_trx_util.get_customer_name(p_customer_id)
AS "ARCustomerName"
,p_gt_inv_num_from
AS "GTInvoiceNumFrom"
,p_gt_inv_num_to
AS "GTInvoiceNumTo"
,ar_gta_trx_util.To_Xsd_Date_String(l_gt_inv_date_from)
AS "GTDateFrom"
,ar_gta_trx_util.To_Xsd_Date_String(l_gt_inv_date_to)
AS "GTDateTo"
,p_ar_inv_num_from
AS "ARTrxNumberFrom"
,p_ar_inv_num_to
AS "ARTrxNumberTo"
,ar_gta_trx_util.To_Xsd_Date_String(l_ar_inv_date_from)
AS "ARTrxDateFrom"
,ar_gta_trx_util.To_Xsd_Date_String(l_ar_inv_date_to)
AS "ARTrxDateTo"))
INTO l_parameter
FROM dual;
SELECT xmlelement("MappingReport"
,xmlconcat(xmlelement("ReportFailed"
,'Y')
,xmlelement("FailedWithParameters"
,'N')
,xmlelement("RepDate"
,ar_gta_trx_util.To_Xsd_Date_String(SYSDATE))
,xmlelement("ReportFailedMsg"
,l_ar_gta_gta_not_enabled)
,l_parameter))
INTO l_report
FROM dual;
SELECT xmlelement("DiscrepancyReport"
,xmlforest('Y' AS "ReportFailed"
,l_ar_gta_not_enabled_msg AS
"ReportFailedMsg"
,'N' AS "FailedWithParameters"))
INTO l_report_xml
FROM dual;
SELECT xmlelement("Parameters"
,xmlforest(ar_gta_trx_util.get_operatingunit(l_org_id)
AS "OperationUnit"
, p_gl_period AS "GLPeriod"
, p_customer_num_from AS "CustomerNumFrom"
, p_customer_num_to AS "CustomerNumTo"
, p_customer_name_from AS "CustomerNameFrom"
, p_customer_name_to AS "CustomerNameTo"
, p_consol_trx_num_from AS "ConsolidationTrxNumFrom"
, p_consol_trx_num_to AS "ConsolidationTrxNumTo"
, p_invoice_type AS "InvoiceType"))
INTO l_parameter
FROM dual;
SELECT xmlelement("MappingReport"
,xmlconcat(xmlelement("ReportFailed", 'Y')
,xmlelement("FailedWithParameters",'N')
,xmlelement("RepDate",ar_gta_trx_util.To_Xsd_Date_String(SYSDATE))
,xmlelement("ReportFailedMsg",l_ar_gta_gta_not_enabled)
,l_parameter))
INTO l_report
FROM dual;