[Home] [Help]
37: action_code csd_product_transactions.action_code%type,
38: source_serial_number csd_product_transactions.source_serial_number%type,
39: source_instance_id csd_product_transactions.source_instance_id%type,
40: prod_txn_recd_qty csd_product_transactions.quantity_received%type,
41: estimate_quantity cs_estimate_details.quantity_required%type ,
42: est_order_line_id cs_estimate_details.order_line_id%type ,
43: prod_txn_item_id cs_estimate_details.inventory_item_id%type
44: );
45:
38: source_serial_number csd_product_transactions.source_serial_number%type,
39: source_instance_id csd_product_transactions.source_instance_id%type,
40: prod_txn_recd_qty csd_product_transactions.quantity_received%type,
41: estimate_quantity cs_estimate_details.quantity_required%type ,
42: est_order_line_id cs_estimate_details.order_line_id%type ,
43: prod_txn_item_id cs_estimate_details.inventory_item_id%type
44: );
45:
46: TYPE IO_RCPT_LINES_Rec_Type IS RECORD
39: source_instance_id csd_product_transactions.source_instance_id%type,
40: prod_txn_recd_qty csd_product_transactions.quantity_received%type,
41: estimate_quantity cs_estimate_details.quantity_required%type ,
42: est_order_line_id cs_estimate_details.order_line_id%type ,
43: prod_txn_item_id cs_estimate_details.inventory_item_id%type
44: );
45:
46: TYPE IO_RCPT_LINES_Rec_Type IS RECORD
47: (
83: repair_number csd_repairs.repair_number%type,
84: repair_line_id csd_repairs.repair_line_id%type,
85: ro_uom csd_repairs.unit_of_measure%type ,
86: ro_item_id csd_repairs.inventory_item_id%type ,
87: estimate_quantity cs_estimate_details.quantity_required%type ,
88: prod_txn_serial_num csd_product_transactions.source_serial_number%type ,
89: source_instance_id csd_product_transactions.source_instance_id%type,
90: product_transaction_id csd_product_transactions.product_transaction_id%type,
91: action_code csd_product_transactions.action_code%type,
2141: abs(ced.quantity_required) estimate_quantity,
2142: ced.order_line_id est_order_line_id,
2143: ced.inventory_item_id prod_txn_item_id
2144: FROM csd_product_transactions cpt,
2145: cs_estimate_details ced,
2146: csd_repairs cra,
2147: rcv_transactions rcvt,
2148: oe_order_headers_all oeh,
2149: oe_order_lines_all oel,
2218: abs(ced.quantity_required) estimate_quantity,
2219: ced.order_line_id est_order_line_id,
2220: ced.inventory_item_id prod_txn_item_id
2221: FROM csd_product_transactions cpt,
2222: cs_estimate_details ced,
2223: csd_repairs cra,
2224: rcv_transactions rcvt,
2225: oe_order_headers_all oeh,
2226: oe_order_lines_all oel,
2294: csd_repairs cra,
2295: oe_order_headers_all oeh,
2296: oe_order_lines_all oel,
2297: rcv_transactions rcvt,
2298: cs_estimate_details ced,
2299: csd_product_transactions cpt
2300: WHERE cpt.repair_line_id = p_repair_line_id
2301: AND cpt.action_type in ('RMA', 'RMA_THIRD_PTY') -- excluded walk-in-receipt as it is going off
2302: AND cpt.prod_txn_status in ( 'BOOKED', 'SUBMITTED')
2339: --- cursor for Cancelled orders...
2340: CURSOR Cur_Cancelled_repair_lines IS
2341: SELECT cra.REPAIR_LINE_ID
2342: FROM csd_repairs cra,
2343: cs_estimate_details ced,
2344: csd_product_transactions cpt
2345: WHERE cpt.action_type in ('RMA', 'RMA_THIRD_PTY')
2346: AND cpt.prod_txn_status in ( 'BOOKED', 'SUBMITTED')
2347: AND ced.order_header_id is not null
6179: wnd.name delivery_name,
6180: hao.name org_name
6181: from
6182: csd_product_transactions cpt,
6183: cs_estimate_details ced,
6184: csd_repairs cra,
6185: wsh_delivery_details dd ,
6186: wsh_serial_numbers dsn,--Added to fix 3801614
6187: --Changed to view from table, bug: 4341784
6255: wnd.name delivery_name,
6256: hao.name org_name
6257: from
6258: csd_product_transactions cpt,
6259: cs_estimate_details ced,
6260: csd_repairs cra,
6261: wsh_delivery_details dd ,
6262: wsh_serial_numbers dsn,--Added to fix 3801614
6263: --Changed to view from table, bug: 4341784
6339: wnd.name delivery_name,
6340: hao.name org_name
6341: from
6342: csd_product_transactions cpt,
6343: cs_estimate_details ced,
6344: csd_repairs cra,
6345: wsh_delivery_details dd ,
6346: wsh_serial_numbers dsn,--Added to fix 3801614
6347: --Changed to view from table, bug: 4341784
6381: --- cursor for Cancelled orders...
6382: CURSOR Cur_Cancelled_repair_lines IS
6383: SELECT cra.REPAIR_LINE_ID
6384: FROM csd_repairs cra,
6385: cs_estimate_details ced,
6386: csd_product_transactions cpt
6387: WHERE cpt.action_type in ('SHIP', 'SHIP_THIRD_PTY')
6388: AND cpt.prod_txn_status in ( 'BOOKED', 'SUBMITTED')
6389: AND ced.order_header_id is not null
9356:
9357: CURSOR c_product_transaction_id(p_repair_line_id IN number) is
9358: SELECT c.product_transaction_id, a.booked_flag
9359: FROM oe_order_lines_all a,
9360: cs_estimate_details b,
9361: csd_product_transactions c
9362: WHERE a.line_id = b.order_line_id
9363: AND b.estimate_detail_id = c.estimate_detail_id
9364: and c.prod_txn_status = 'SUBMITTED'
9366: and c.book_sales_order_flag = 'N'
9367: and b.order_header_id in
9368: (
9369: select p.order_header_id
9370: from cs_estimate_details p, csd_product_transactions q
9371: where p.estimate_detail_id=q.estimate_detail_id
9372: and q.repair_line_id=p_repair_line_id
9373: );
9374:
9417: CURSOR CANCELLED_ORDER_LINES(p_repair_line_id NUMBER) IS
9418: SELECT DISTINCT cpt.product_transaction_id PRODUCT_TXN_ID
9419: FROM oe_order_headers_all oeh,
9420: oe_order_lines_all oel,
9421: cs_estimate_details ced,
9422: csd_product_transactions cpt
9423: WHERE cpt.repair_line_id = p_repair_line_id
9424: AND cpt.action_type in ('RMA', 'SHIP', 'RMA_THIRD_PTY', 'SHIP_THIRD_PTY')
9425: AND cpt.prod_txn_status in ( 'BOOKED', 'SUBMITTED')
9527: est.original_source_id,
9528: est.line_category_code,
9529: est.order_header_id
9530: from
9531: cs_estimate_details est
9532: where
9533: est.order_line_id = p_line_id;
9534:
9535: BEGIN