APPS.JE_REVERSE_SALES_LIST_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 47

  SELECT ADD_MONTHS(TO_DATE(P_FROM_DATE,'YYYY/MM/DD HH24:MI:SS'),ROWNUM-1) PERIOD
  FROM  fnd_application
  WHERE ROWNUM <=DECODE( ROUND(MONTHS_BETWEEN(TRUNC(TO_DATE(P_TO_DATE,'YYYY/MM/DD HH24:MI:SS')),
	                               TRUNC(TO_DATE(P_FROM_DATE,'YYYY/MM/DD HH24:MI:SS')))),0,1, ROUND(MONTHS_BETWEEN(TRUNC(TO_DATE(P_TO_DATE,'YYYY/MM/DD HH24:MI:SS')),
	                               TRUNC(TO_DATE(P_FROM_DATE,'YYYY/MM/DD HH24:MI:SS')))));

Line: 53

  SELECT
    DISTINCT ctrx.bill_to_customer_id CUSTOMER_ID,
    hca.account_number CUSTOMER_NUMBER,
    hp.party_name CUSTOMER_NAME,
    zl.tax_registration_number CUST_VAT_REG_NUM
      FROM
      hz_cust_accounts hca,
      hz_parties hp,
      ra_customer_trx_all ctrx,
      zx_lines zl
       WHERE ctrx.legal_entity_id = P_LEGAL_ENTITY
          AND ctrx.complete_flag = 'Y'
	  AND TRUNC(ctrx.trx_date) BETWEEN TRUNC(TO_DATE(P_FROM_DATE,'YYYY/MM/DD HH24:MI:SS'))
          AND TRUNC(TO_DATE(P_TO_DATE,'YYYY/MM/DD HH24:MI:SS'))
	  AND ctrx.bill_to_customer_id = hca.cust_account_id
	  AND hca.party_id = hp.party_id
	  AND zl.trx_id = ctrx.customer_trx_id
          AND zl.hq_estb_reg_number = P_TAX_REG_NUM
          AND zl.legal_entity_id = P_LEGAL_ENTITY
          AND zl.application_id = 222
	    ORDER BY CUSTOMER_NAME;

Line: 114

        SELECT ROUND(NVL(SUM(trx_lines.extended_amount),0))
          INTO ln_sales_amount
        FROM ra_customer_trx_all trx,
             ra_customer_trx_lines_all trx_lines,
             zx_lines lines,
	     ra_customer_trx_lines_all trx_tax_lines,
             zx_report_codes_assoc zrc,
	     zx_reporting_types_vl zrt
        WHERE trx_lines.line_type ='LINE'
	  AND trx_lines.customer_trx_id = trx.customer_trx_id
	  AND trx_tax_lines.line_type ='TAX'
          AND trx_lines.customer_trx_line_id = trx_tax_lines.link_to_cust_trx_line_id
          AND trx_tax_lines.customer_trx_id = trx.customer_trx_id
          AND trx.bill_to_customer_id = rec_customers.CUSTOMER_ID
          AND TRUNC(trx.trx_date) BETWEEN TRUNC(ld_from_date) AND TRUNC(ld_to_date)
          AND trx.legal_entity_id =P_LEGAL_ENTITY
          AND trx.complete_flag ='Y'
	  AND lines.trx_line_id = trx_lines.customer_trx_line_id
          AND lines.trx_id      = trx.customer_trx_id
          AND lines.hq_estb_reg_number =P_TAX_REG_NUM
          AND NVL(lines.tax_registration_number,-99) = NVL(rec_customers.CUST_VAT_REG_NUM,-99)
          AND lines.legal_entity_id =P_LEGAL_ENTITY
          AND lines.application_id =222
          AND trx_tax_lines.vat_tax_id = zrc.entity_id
	  AND zrc.reporting_type_id = zrt.reporting_type_id
	  AND DECODE(zrt.reporting_type_datatype_code,'TEXT',SUBSTR(UPPER(zrc.reporting_code_char_value),1,1),'YES_NO',zrc.reporting_code_char_value,'N') = 'Y' -- Not sure what kind of set up user will do
	  AND TRUNC(trx.trx_date) BETWEEN TRUNC(zrc.effective_from) AND TRUNC(NVL(zrc.effective_to,trx.trx_date))
          AND zrt.reporting_type_code ='REVERSE_CHARGE_VAT';

Line: 159

        INSERT INTO je_uk_sales_trx_gt
	(
        je_info_v1,--Identifier
	je_info_n1,--CustId
	je_info_v2,--CustNum
	je_info_v3,--CustName
	je_info_v4,--CustVATRegNum
	je_info_v5,--Mon-YYYY
	je_info_n2,--SalesAmt
	je_info_d1 --Date
	)
	VALUES
	(
	'CL',
	rec_customers.CUSTOMER_ID,
	rec_customers.CUSTOMER_NUMBER,
	rec_customers.CUSTOMER_NAME,
	lv_cust_vat_reg_num,
	TO_CHAR(rec_period_range.PERIOD,'Mon-YYYY'),
	ln_sales_amount,
	ld_from_date
	);

Line: 184

	FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JE.plsql.JE_REVERSE_SALES_LIST_PKG.beforeReport','Exception in Inserting in GT for CL');

Line: 189

      DELETE je_uk_sales_trx_gt
        WHERE je_info_v1 = 'CL'
          AND je_info_n1 = rec_customers.CUSTOMER_ID
          AND je_info_v2 = rec_customers.CUSTOMER_NUMBER
	  AND je_info_v4 = lv_cust_vat_reg_num;

Line: 195

        FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JE.plsql.JE_REVERSE_SALES_LIST_PKG.beforeReport','In DELETE je_uk_sales_trx_gt');

Line: 199

        INSERT INTO je_uk_sales_trx_gt
        (
        je_info_v1,
        je_info_v2
        )
        VALUES
        (
        'CS',
        lv_dynamic_sales
        );

Line: 213

          FND_LOG.STRING(G_LEVEL_EXCEPTION,'JE.plsql.JE_REVERSE_SALES_LIST_PKG.beforeReport','In INSERT je_uk_sales_trx_gt for type CS');