APPS.MSC_X_PEGGING_FUNC SQL Statements

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

Line: 64

      SELECT publisher_order_type
        INTO var_order_type
        FROM msc_sup_dem_entries_ui_v -- msc_sup_dem_entries_ui_v
       WHERE transaction_id = arg_transid ;

Line: 77

      SELECT a.transaction_id
        BULK COLLECT INTO var_ids
        FROM msc_sup_dem_entries a -- msc_sup_dem_entries_ui_v
       WHERE exists
                 (select 1 from msc_sup_dem_security_v security
                  where security.transaction_id = a.transaction_id )
         AND Level < RECEIPT_LEVEL
       START WITH a.transaction_id = var_start_id
       CONNECT BY PRIOR a.order_number = a.end_order_number
          AND PRIOR nvl(a.release_number, -1) = nvl(a.end_order_rel_number, -1)
          AND (
               (a.end_order_line_number IS NOT NULL AND
                PRIOR a.line_number = a.end_order_line_number )
               OR
               (a.end_order_line_number IS NULL AND
                PRIOR a.publisher_id = a.end_order_publisher_id AND
                decode(a.end_order_publisher_site_id,
                         null, PRIOR a.publisher_site_id,
                         a.end_order_publisher_site_id) = PRIOR a.publisher_site_id AND
                PRIOR a.inventory_item_id = a.inventory_item_id )
               OR
               (a.end_order_line_number IS NULL AND
                a.end_order_publisher_id <> a.publisher_id AND
                PRIOR a.inventory_item_id = a.inventory_item_id )
             )
           AND (
                (a.end_order_publisher_id IS NOT NULL AND
                 PRIOR a.publisher_id = a.end_order_publisher_id AND
                 a.end_order_type IS NOT NULL AND
                 PRIOR a.publisher_order_type = a.end_order_type AND
                 decode(a.end_order_publisher_site_id,
                         null, PRIOR a.publisher_site_id,
                         a.end_order_publisher_site_id) = PRIOR a.publisher_site_id
                )
                OR
                (a.end_order_publisher_id IS NULL AND
                 a.end_order_type IS NOT NULL AND
                 PRIOR a.publisher_id = a.publisher_id)
              ) ;

Line: 149

      SELECT count(a.transaction_id)
        INTO v_ret_num
        FROM msc_sup_dem_entries a-- msc_sup_dem_entries_ui_v
       WHERE exists
                  (select 1 from msc_sup_dem_security_v security
                  where security.transaction_id = a.transaction_id )
         AND a.transaction_id <> arg_transid  -- else the starting rec will be counted as one
       START WITH a.transaction_id = arg_transid
       CONNECT BY
           PRIOR a.order_number = a.end_order_number
       AND PRIOR nvl(a.release_number, -1) = nvl(a.end_order_rel_number, -1)
       AND (
             (a.end_order_line_number IS NOT NULL AND
              PRIOR a.line_number = a.end_order_line_number )
             OR
             (a.end_order_line_number IS NULL AND
              PRIOR a.publisher_id = a.end_order_publisher_id AND
              decode(a.end_order_publisher_site_id,
                       null, PRIOR a.publisher_site_id,
                       a.end_order_publisher_site_id) = PRIOR a.publisher_site_id AND
              PRIOR a.inventory_item_id = a.inventory_item_id )
             OR
             (a.end_order_line_number IS NULL AND
              a.end_order_publisher_id <> a.publisher_id AND
              PRIOR a.inventory_item_id = a.inventory_item_id )
           )
        AND (
              (a.end_order_publisher_id IS NOT NULL AND
               PRIOR a.publisher_id = a.end_order_publisher_id AND
               a.end_order_type IS NOT NULL AND
               PRIOR a.publisher_order_type = a.end_order_type AND
               decode(a.end_order_publisher_site_id,
                       null, PRIOR a.publisher_site_id,
                       a.end_order_publisher_site_id) = PRIOR a.publisher_site_id
              )
              OR
              (a.end_order_publisher_id IS NULL AND
               a.end_order_type IS NOT NULL AND
               PRIOR a.publisher_id = a.publisher_id)
            ) ;

Line: 219

      Select publisher_order_type, receipt_date
        Into v_order_type, v_receipt_date
        From msc_sup_dem_entries_ui_v
      Where transaction_id = arg_transid
        and publisher_order_type in (PURCHASE_ORDER, SALES_ORDER, ASN);

Line: 232

         SELECT max(a.receipt_date)
           INTO v_receipt_date
           FROM msc_sup_dem_entries a
          WHERE a.plan_id = -1
            AND a.publisher_order_type = SALES_ORDER
            AND exists
                 (select 1 from msc_sup_dem_security_v security
                  where security.transaction_id = a.transaction_id )
            AND Level < LEVEL_3
           START with a.transaction_id = arg_transid
         CONNECT BY PRIOR a.order_number = a.end_order_number
          AND PRIOR nvl(a.release_number, -1) = nvl(a.end_order_rel_number, -1)
          AND (
               (a.end_order_line_number IS NOT NULL AND
                PRIOR a.line_number = a.end_order_line_number )
               OR
               (a.end_order_line_number IS NULL AND
                PRIOR a.publisher_id = a.end_order_publisher_id AND
                decode(a.end_order_publisher_site_id,
                         null, PRIOR a.publisher_site_id,
                         a.end_order_publisher_site_id) = PRIOR a.publisher_site_id AND
                PRIOR a.inventory_item_id = a.inventory_item_id )
               OR
               (a.end_order_line_number IS NULL AND
                a.end_order_publisher_id <> a.publisher_id AND
                PRIOR a.inventory_item_id = a.inventory_item_id )
             )
           AND (
                (a.end_order_publisher_id IS NOT NULL AND
                 PRIOR a.publisher_id = a.end_order_publisher_id AND
                 a.end_order_type IS NOT NULL AND
                 PRIOR a.publisher_order_type = a.end_order_type AND
                 decode(a.end_order_publisher_site_id,
                         null, PRIOR a.publisher_site_id,
                         a.end_order_publisher_site_id) = PRIOR a.publisher_site_id
                )
                OR
                (a.end_order_publisher_id IS NULL AND
                 a.end_order_type IS NOT NULL AND
                 PRIOR a.publisher_id = a.publisher_id)
              ) ;

Line: 313

      Select publisher_order_type,
                decode(publisher_order_type,PURCHASE_ORDER,receipt_date,null),
                decode(publisher_order_type,SALES_ORDER,receipt_date,null)
        Into v_order_type, v_need_by_date, v_receipt_date
        From msc_sup_dem_entries_ui_v
      Where transaction_id = arg_transid
        and publisher_order_type in (PURCHASE_ORDER, SALES_ORDER);

Line: 325

         Select receipt_date
           Into v_need_by_date
           From msc_sup_dem_entries_ui_v
         Where transaction_id = v_start_id ;

Line: 366

      Select publisher_order_type
        Into v_order_type
        From msc_sup_dem_entries_ui_v
      Where transaction_id = arg_transid ;

Line: 375

         SELECT max(get_days_late(a.transaction_id))
           INTO v_max_late
           FROM msc_sup_dem_entries a
          WHERE a.plan_id = -1
            AND a.publisher_order_type = PURCHASE_ORDER
            AND exists
                 (select 1 from msc_sup_dem_security_v security
                  where security.transaction_id = a.transaction_id )
           START with a.transaction_id = arg_transid
       CONNECT BY PRIOR a.order_number = a.end_order_number
          AND PRIOR nvl(a.release_number, -1) = nvl(a.end_order_rel_number, -1)
          AND (
               (a.end_order_line_number IS NOT NULL AND
                PRIOR a.line_number = a.end_order_line_number )
               OR
               (a.end_order_line_number IS NULL AND
                PRIOR a.publisher_id = a.end_order_publisher_id AND
                decode(a.end_order_publisher_site_id,
                         null, PRIOR a.publisher_site_id,
                         a.end_order_publisher_site_id) = PRIOR a.publisher_site_id AND
                PRIOR a.inventory_item_id = a.inventory_item_id )
               OR
               (a.end_order_line_number IS NULL AND
                a.end_order_publisher_id <> a.publisher_id AND
                PRIOR a.inventory_item_id = a.inventory_item_id )
             )
           AND (
                (a.end_order_publisher_id IS NOT NULL AND
                 PRIOR a.publisher_id = a.end_order_publisher_id AND
                 a.end_order_type IS NOT NULL AND
                 PRIOR a.publisher_order_type = a.end_order_type AND
                 decode(a.end_order_publisher_site_id,
                         null, PRIOR a.publisher_site_id,
                         a.end_order_publisher_site_id) = PRIOR a.publisher_site_id
                )
                OR
                (a.end_order_publisher_id IS NULL AND
                 a.end_order_type IS NOT NULL AND
                 PRIOR a.publisher_id = a.publisher_id)
              ) ;

Line: 441

      SELECT sd.transaction_id
        INTO v_transid
        FROM msc_sup_dem_entries sd -- msc_sup_dem_entries_ui_v
       WHERE sd.publisher_order_type = PURCHASE_ORDER
         AND sd.plan_id = -1
         AND exists
              (select 1 from msc_sup_dem_security_v security
               where security.transaction_id = sd.transaction_id )
         AND level = v_level
       START WITH transaction_id = arg_transid
      CONNECT BY
          sd.order_number = PRIOR sd.end_order_number
      AND ( (PRIOR sd.end_order_line_number IS NOT NULL AND
             PRIOR sd.end_order_line_number = sd.line_number)
            OR
            (PRIOR sd.end_order_line_number IS NULL AND
             sd.publisher_id = PRIOR sd.end_order_publisher_id AND
             decode(PRIOR sd.end_order_publisher_site_id, null,
                    sd.publisher_site_id,
                 PRIOR sd.end_order_publisher_site_id)
              = sd.publisher_site_id  AND
            PRIOR sd.inventory_item_id = sd.inventory_item_id )
            OR
            (PRIOR sd.end_order_line_number IS NULL AND
             PRIOR sd.publisher_id <> PRIOR sd.end_order_publisher_id)
          )
      AND nvl(sd.release_number, -1)
               = nvl(PRIOR sd.end_order_rel_number, -1)
      AND ((PRIOR sd.end_order_publisher_id IS NOT NULL AND
            PRIOR sd.end_order_type IS NOT NULL AND
            PRIOR sd.end_order_type = sd.publisher_order_type AND
            PRIOR sd.end_order_publisher_id = sd.publisher_id AND
            decode(PRIOR sd.end_order_publisher_site_id, null,
                  sd.publisher_site_id,
                PRIOR sd.end_order_publisher_site_id)
                = sd.publisher_site_id
            )
            OR
            (PRIOR sd.end_order_publisher_id IS NULL AND
             PRIOR sd.end_order_type IS NOT NULL AND
             PRIOR sd.publisher_id = sd.publisher_id) )
       and rownum = 1;

Line: 499

      SELECT publisher_order_type,
             decode(publisher_order_type,13,receipt_date,null),
             decode(publisher_order_type,14,receipt_date,null),
             decode(sys_context('MSC','COMPANY_ID'),
                                     publisher_id, primary_quantity,
                                     customer_id, tp_quantity,
                                     supplier_id, tp_quantity,
                                     quantity)
        INTO v_order_type, v_need_by_date, v_receipt_date, v_ontime_qty
        FROM msc_sup_dem_entries_ui_v
       WHERE transaction_id = arg_transid;

Line: 522

         SELECT receipt_date
           INTO v_need_by_date
           FROM msc_sup_dem_entries_ui_v
          WHERE transaction_id = v_start_id ;

Line: 538

         SELECT sum(decode(sys_context('MSC','COMPANY_ID'),
                         publisher_id, primary_quantity,
                         customer_id, tp_quantity,
                         supplier_id, tp_quantity,
                         quantity) )
           INTO v_ontime_qty
           FROM msc_sup_dem_entries a
          WHERE a.plan_id = -1
            AND a.publisher_order_type = SALES_ORDER
            AND exists
                 (select 1 from msc_sup_dem_security_v security
                  where security.transaction_id = a.transaction_id )
            AND a.receipt_date <= v_need_by_date
            AND LEVEL < LEVEL_3
           START with a.transaction_id = arg_transid
         CONNECT BY PRIOR a.order_number = a.end_order_number
             AND PRIOR nvl(a.release_number, -1) = nvl(a.end_order_rel_number, -1)
             AND (
                  (a.end_order_line_number IS NOT NULL AND
                   PRIOR a.line_number = a.end_order_line_number )
                  OR
                  (a.end_order_line_number IS NULL AND
                   PRIOR a.publisher_id = a.end_order_publisher_id AND
                   decode(a.end_order_publisher_site_id,
                            null, PRIOR a.publisher_site_id,
                            a.end_order_publisher_site_id) = PRIOR a.publisher_site_id AND
                   PRIOR a.inventory_item_id = a.inventory_item_id )
                  OR
                  (a.end_order_line_number IS NULL AND
                   a.end_order_publisher_id <> a.publisher_id AND
                   PRIOR a.inventory_item_id = a.inventory_item_id )
                 )
             AND (
                  (a.end_order_publisher_id IS NOT NULL AND
                   PRIOR a.publisher_id = a.end_order_publisher_id AND
                   a.end_order_type IS NOT NULL AND
                   PRIOR a.publisher_order_type = a.end_order_type AND
                   decode(a.end_order_publisher_site_id,
                           null, PRIOR a.publisher_site_id,
                           a.end_order_publisher_site_id) = PRIOR a.publisher_site_id
                  )
                  OR
                  (a.end_order_publisher_id IS NULL AND
                   a.end_order_type IS NOT NULL AND
                   PRIOR a.publisher_id = a.publisher_id)
                 ) ;

Line: 603

      SELECT publisher_order_type,
             decode(publisher_order_type,PURCHASE_ORDER,receipt_date,null),
             decode(publisher_order_type,SALES_ORDER,receipt_date,null),
             decode(sys_context('MSC','COMPANY_ID'),
                                     publisher_id, primary_quantity,
                                     customer_id, tp_quantity,
                                     supplier_id, tp_quantity,
                                     quantity)
        INTO v_order_type, v_need_by_date, v_receipt_date, v_late_qty
        FROM msc_sup_dem_entries_ui_v
       WHERE transaction_id = arg_transid;

Line: 628

         SELECT receipt_date
           INTO v_need_by_date
           FROM msc_sup_dem_entries_ui_v
          WHERE transaction_id = v_start_id ;

Line: 645

         SELECT sum(decode(sys_context('MSC','COMPANY_ID'),
                         publisher_id, primary_quantity,
                         customer_id, tp_quantity,
                         supplier_id, tp_quantity,
                         quantity) )
           INTO v_late_qty
           FROM msc_sup_dem_entries a
          WHERE a.plan_id = -1
            AND a.publisher_order_type = SALES_ORDER
            AND exists
                 (select 1 from msc_sup_dem_security_v security
                  where security.transaction_id = a.transaction_id )
            AND a.receipt_date > v_need_by_date
            AND LEVEL < LEVEL_3
           START with a.transaction_id = arg_transid
         CONNECT BY PRIOR a.order_number = a.end_order_number
             AND PRIOR nvl(a.release_number, -1) = nvl(a.end_order_rel_number, -1)
             AND (
                  (a.end_order_line_number IS NOT NULL AND
                   PRIOR a.line_number = a.end_order_line_number )
                  OR
                  (a.end_order_line_number IS NULL AND
                   PRIOR a.publisher_id = a.end_order_publisher_id AND
                   decode(a.end_order_publisher_site_id,
                            null, PRIOR a.publisher_site_id,
                            a.end_order_publisher_site_id) = PRIOR a.publisher_site_id AND
                   PRIOR a.inventory_item_id = a.inventory_item_id )
                  OR
                  (a.end_order_line_number IS NULL AND
                   a.end_order_publisher_id <> a.publisher_id AND
                   PRIOR a.inventory_item_id = a.inventory_item_id )
                 )
             AND (
                  (a.end_order_publisher_id IS NOT NULL AND
                   PRIOR a.publisher_id = a.end_order_publisher_id AND
                   a.end_order_type IS NOT NULL AND
                   PRIOR a.publisher_order_type = a.end_order_type AND
                   decode(a.end_order_publisher_site_id,
                           null, PRIOR a.publisher_site_id,
                           a.end_order_publisher_site_id) = PRIOR a.publisher_site_id
                  )
                  OR
                  (a.end_order_publisher_id IS NULL AND
                   a.end_order_type IS NOT NULL AND
                   PRIOR a.publisher_id = a.publisher_id)
                 ) ;

Line: 707

      SELECT publisher_order_type,
             decode(sys_context('MSC','COMPANY_ID'),
                                   publisher_id, primary_quantity,
                                   customer_id, tp_quantity,
                                   supplier_id, tp_quantity,
                      quantity)
        INTO v_order_type, v_intransit_qty
        FROM msc_sup_dem_entries_ui_v
       WHERE transaction_id = arg_transid
         AND publisher_order_type in (PURCHASE_ORDER, ASN);

Line: 725

         SELECT sum(decode(sys_context('MSC','COMPANY_ID'),
                         publisher_id, primary_quantity,
                         customer_id, tp_quantity,
                         supplier_id, tp_quantity,
                         quantity) )
           INTO v_intransit_qty
           FROM msc_sup_dem_entries a
          WHERE a.plan_id = -1
            AND a.publisher_order_type = ASN
            AND exists
                 (select 1 from msc_sup_dem_security_v security
                  where security.transaction_id = a.transaction_id )
            AND LEVEL < LEVEL_4
           START with a.transaction_id = arg_transid
         CONNECT BY PRIOR a.order_number = a.end_order_number
             AND PRIOR nvl(a.release_number, -1) = nvl(a.end_order_rel_number, -1)
             AND (
                  (a.end_order_line_number IS NOT NULL AND
                   PRIOR a.line_number = a.end_order_line_number )
                  OR
                  (a.end_order_line_number IS NULL AND
                   PRIOR a.publisher_id = a.end_order_publisher_id AND
                   decode(a.end_order_publisher_site_id,
                            null, PRIOR a.publisher_site_id,
                            a.end_order_publisher_site_id) = PRIOR a.publisher_site_id AND
                   PRIOR a.inventory_item_id = a.inventory_item_id )
                  OR
                  (a.end_order_line_number IS NULL AND
                   a.end_order_publisher_id <> a.publisher_id AND
                   PRIOR a.inventory_item_id = a.inventory_item_id )

                 )
             AND (
                  (a.end_order_publisher_id IS NOT NULL AND
                   PRIOR a.publisher_id = a.end_order_publisher_id AND
                   a.end_order_type IS NOT NULL AND
                   PRIOR a.publisher_order_type = a.end_order_type AND
                   decode(a.end_order_publisher_site_id,
                           null, PRIOR a.publisher_site_id,
                           a.end_order_publisher_site_id) = PRIOR a.publisher_site_id
                  )
                  OR
                  (a.end_order_publisher_id IS NULL AND
                   a.end_order_type IS NOT NULL AND
                   PRIOR a.publisher_id = a.publisher_id)
                 ) ;

Line: 786

      SELECT publisher_order_type,
             decode(sys_context('MSC','COMPANY_ID'),
                                   publisher_id, primary_quantity,
                                   customer_id, tp_quantity,
                                   supplier_id, tp_quantity,
                      quantity)
        INTO v_order_type, v_po_qty
        FROM msc_sup_dem_entries_ui_v
       WHERE transaction_id = arg_transid;