APPS.AP_UPDATE_1099_AWT_DISTS_PKG SQL Statements

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

Line: 21

    SELECT  SUBSTR(state, 1, 10)
    INTO    l_state
    FROM    po_vendor_sites   PVS,
            ap_invoices       AI
    WHERE   AI.invoice_id       = P_invoice_id
    AND     PVS.vendor_site_id  = AI.vendor_site_id;

Line: 82

           'Ap_Update_1099_Awt_Dists_Pkg.Upgrade';

Line: 84

    SELECT  TO_CHAR(SYSDATE, 'YYYY/MM/DD' )
    INTO    l_date
    FROM    dual;

Line: 112

    SELECT NVL(enable_1099_on_awt_flag, 'N'),
           combined_filing_flag,
           income_tax_region_flag,
           income_tax_region
    INTO   l_enable_1099_on_awt_flag,
           l_combined_filing_flag,
           l_income_tax_region_flag,
           l_income_tax_region
    FROM   ap_system_parameters;

Line: 122

    SELECT NVL(MIN(invoice_distribution_id),0),
           NVL(MAX(invoice_distribution_id),0)
    INTO   l_min_invoice_distribution_id, l_max_invoice_distribution_id
    FROM   ap_invoice_distributions ID,
           ap_invoices AI,
           po_vendor_sites PVS,
           po_vendors PV
    WHERE  ID.invoice_id = AI.invoice_id
    AND    AI.vendor_site_id = PVS.vendor_site_id
    AND    PV.vendor_id = PVS.vendor_id
    AND    PV.federal_reportable_flag =  'Y'
    AND    PVS.tax_reporting_site_flag = 'Y'
    AND    NVL(ID.type_1099, 'DUMMY') <> 'MISC4'
    AND    ID.line_type_lookup_code = 'AWT'
    AND    (ID.invoice_id IN (SELECT IP.invoice_id
                              FROM   AP_Invoice_Payments IP
                              WHERE  ID.invoice_id = IP.invoice_id
                              AND    nvl(IP.accounting_date,sysdate)
                                       BETWEEN l_start_date AND sysdate
                                 )
           OR
           ID.invoice_id IN (SELECT  AI.invoice_id
                             FROM    Ap_Invoices AI
                             WHERE   ID.invoice_id = AI.invoice_id
                             AND     NVL(AI.PAYMENT_STATUS_FLAG, 'N') <> 'Y'
                             )
           );

Line: 154

        l_debug_info := 'Update TYPE_1099 on AID';

Line: 156

        UPDATE  ap_invoice_distributions ID
        SET     ID.type_1099 = 'MISC4',
                ID.income_tax_Region =
                    decode(l_combined_filing_flag, 'Y',
                           decode(l_income_tax_region_flag, 'Y',
                           Ap_Update_1099_Awt_Dists_Pkg.Get_Income_tax_region(
                                                    ID.invoice_id,
                                                    l_current_calling_sequence),
                           l_income_tax_region
                                  ), NULL
                           ),
                ID.last_update_date = SYSDATE,
                ID.last_updated_by = l_user_id,
                ID.last_update_login = l_login_id,
                ID.program_update_date = SYSDATE,
                ID.program_application_id = l_program_application_id,
                ID.program_id = l_program_id,
                ID.request_id = l_request_id
        WHERE   ID.invoice_id IN (
                   SELECT    AI.invoice_id
                   FROM      ap_invoices AI,
                             po_vendors PV,
                             po_vendor_sites PVS
                   WHERE     AI.vendor_id      = PV.vendor_id
                   AND       AI.vendor_site_id = PVS.vendor_site_id
                   AND       PV.vendor_id = PVS.vendor_id
                   AND       PV.federal_reportable_flag =  'Y'
                   AND       PVS.tax_reporting_site_flag = 'Y'
                                 )
        AND     (ID.invoice_id IN (SELECT IP.invoice_id
                                  FROM   AP_Invoice_Payments IP
                                  WHERE  ID.invoice_id = IP.invoice_id
                                  AND    nvl(IP.accounting_date,sysdate)
                                       BETWEEN l_start_date AND sysdate
                                 )
                OR
                ID.invoice_id IN (SELECT  AI.invoice_id
                                  FROM    Ap_Invoices AI
                                  WHERE   ID.invoice_id = AI.invoice_id
                                  AND     NVL(AI.PAYMENT_STATUS_FLAG, 'N') <> 'Y'
                                  )
                )
        AND    ID.line_type_lookup_code = 'AWT'
        AND    NVL(ID.type_1099, 'DUMMY') <> 'MISC4'
        AND    invoice_distribution_id
                  BETWEEN l_min_invoice_distribution_id
    	          AND     l_min_invoice_distribution_id + l_commit_size - 1 ;

Line: 213

              AP_Debug_Pkg.Print('Y', 'Number of Distributions Updated  : '
                              || TO_CHAR(l_total_count));