352: (i.e) If there are 2 pull sequences which form a chain because the
353: source-subinventory is the supply sub-inventory for the next then
354: only the last in chain will be picked up in this cursor */
355:
356: l_sql_p := dbms_sql.open_cursor;
357: l_sql_stmt :=
358: 'SELECT pull_sequence_id, inventory_item_id,'||
359: 'subinventory_name, locator_id'||
360: ' FROM mtl_kanban_pull_sequences MKP1'||
389: ' and mkp2.subinventory_name <= nvl(:sub_to,mkp2.subinventory_name) '||
390: ' and nvl(mkp2.locator_id,-1) = nvl(mkp1.source_locator_id,-1)) '||
391: ' ORDER by mkp1.inventory_item_id ';
392:
393: dbms_sql.parse( l_sql_p, l_sql_stmt, dbms_sql.native );
394:
395: dbms_sql.define_column(l_sql_p,1,ld_pull_seq_id);
396: dbms_sql.define_column(l_sql_p,2,ld_item_id);
397: dbms_sql.define_column(l_sql_p,3,ld_subinv,10 );
391: ' ORDER by mkp1.inventory_item_id ';
392:
393: dbms_sql.parse( l_sql_p, l_sql_stmt, dbms_sql.native );
394:
395: dbms_sql.define_column(l_sql_p,1,ld_pull_seq_id);
396: dbms_sql.define_column(l_sql_p,2,ld_item_id);
397: dbms_sql.define_column(l_sql_p,3,ld_subinv,10 );
398: dbms_sql.define_column(l_sql_p,4,ld_loc_id);
399:
392:
393: dbms_sql.parse( l_sql_p, l_sql_stmt, dbms_sql.native );
394:
395: dbms_sql.define_column(l_sql_p,1,ld_pull_seq_id);
396: dbms_sql.define_column(l_sql_p,2,ld_item_id);
397: dbms_sql.define_column(l_sql_p,3,ld_subinv,10 );
398: dbms_sql.define_column(l_sql_p,4,ld_loc_id);
399:
400: dbms_sql.bind_variable(l_sql_p,'org_id', arg_org_id);
393: dbms_sql.parse( l_sql_p, l_sql_stmt, dbms_sql.native );
394:
395: dbms_sql.define_column(l_sql_p,1,ld_pull_seq_id);
396: dbms_sql.define_column(l_sql_p,2,ld_item_id);
397: dbms_sql.define_column(l_sql_p,3,ld_subinv,10 );
398: dbms_sql.define_column(l_sql_p,4,ld_loc_id);
399:
400: dbms_sql.bind_variable(l_sql_p,'org_id', arg_org_id);
401: dbms_sql.bind_variable(l_sql_p,'sub_from', arg_subinv_from);
394:
395: dbms_sql.define_column(l_sql_p,1,ld_pull_seq_id);
396: dbms_sql.define_column(l_sql_p,2,ld_item_id);
397: dbms_sql.define_column(l_sql_p,3,ld_subinv,10 );
398: dbms_sql.define_column(l_sql_p,4,ld_loc_id);
399:
400: dbms_sql.bind_variable(l_sql_p,'org_id', arg_org_id);
401: dbms_sql.bind_variable(l_sql_p,'sub_from', arg_subinv_from);
402: dbms_sql.bind_variable(l_sql_p,'sub_to', arg_subinv_to);
396: dbms_sql.define_column(l_sql_p,2,ld_item_id);
397: dbms_sql.define_column(l_sql_p,3,ld_subinv,10 );
398: dbms_sql.define_column(l_sql_p,4,ld_loc_id);
399:
400: dbms_sql.bind_variable(l_sql_p,'org_id', arg_org_id);
401: dbms_sql.bind_variable(l_sql_p,'sub_from', arg_subinv_from);
402: dbms_sql.bind_variable(l_sql_p,'sub_to', arg_subinv_to);
403: dbms_sql.bind_variable(l_sql_p,'source', arg_source_type);
404: dbms_sql.bind_variable(l_sql_p,'supplier_id', arg_supplier_id);
397: dbms_sql.define_column(l_sql_p,3,ld_subinv,10 );
398: dbms_sql.define_column(l_sql_p,4,ld_loc_id);
399:
400: dbms_sql.bind_variable(l_sql_p,'org_id', arg_org_id);
401: dbms_sql.bind_variable(l_sql_p,'sub_from', arg_subinv_from);
402: dbms_sql.bind_variable(l_sql_p,'sub_to', arg_subinv_to);
403: dbms_sql.bind_variable(l_sql_p,'source', arg_source_type);
404: dbms_sql.bind_variable(l_sql_p,'supplier_id', arg_supplier_id);
405: dbms_sql.bind_variable(l_sql_p,'source_org_id', arg_source_org_id);
398: dbms_sql.define_column(l_sql_p,4,ld_loc_id);
399:
400: dbms_sql.bind_variable(l_sql_p,'org_id', arg_org_id);
401: dbms_sql.bind_variable(l_sql_p,'sub_from', arg_subinv_from);
402: dbms_sql.bind_variable(l_sql_p,'sub_to', arg_subinv_to);
403: dbms_sql.bind_variable(l_sql_p,'source', arg_source_type);
404: dbms_sql.bind_variable(l_sql_p,'supplier_id', arg_supplier_id);
405: dbms_sql.bind_variable(l_sql_p,'source_org_id', arg_source_org_id);
406: dbms_sql.bind_variable(l_sql_p,'source_subinv', arg_source_subinv);
399:
400: dbms_sql.bind_variable(l_sql_p,'org_id', arg_org_id);
401: dbms_sql.bind_variable(l_sql_p,'sub_from', arg_subinv_from);
402: dbms_sql.bind_variable(l_sql_p,'sub_to', arg_subinv_to);
403: dbms_sql.bind_variable(l_sql_p,'source', arg_source_type);
404: dbms_sql.bind_variable(l_sql_p,'supplier_id', arg_supplier_id);
405: dbms_sql.bind_variable(l_sql_p,'source_org_id', arg_source_org_id);
406: dbms_sql.bind_variable(l_sql_p,'source_subinv', arg_source_subinv);
407: dbms_sql.bind_variable(l_sql_p,'line_id', arg_line_id);
400: dbms_sql.bind_variable(l_sql_p,'org_id', arg_org_id);
401: dbms_sql.bind_variable(l_sql_p,'sub_from', arg_subinv_from);
402: dbms_sql.bind_variable(l_sql_p,'sub_to', arg_subinv_to);
403: dbms_sql.bind_variable(l_sql_p,'source', arg_source_type);
404: dbms_sql.bind_variable(l_sql_p,'supplier_id', arg_supplier_id);
405: dbms_sql.bind_variable(l_sql_p,'source_org_id', arg_source_org_id);
406: dbms_sql.bind_variable(l_sql_p,'source_subinv', arg_source_subinv);
407: dbms_sql.bind_variable(l_sql_p,'line_id', arg_line_id);
408:
401: dbms_sql.bind_variable(l_sql_p,'sub_from', arg_subinv_from);
402: dbms_sql.bind_variable(l_sql_p,'sub_to', arg_subinv_to);
403: dbms_sql.bind_variable(l_sql_p,'source', arg_source_type);
404: dbms_sql.bind_variable(l_sql_p,'supplier_id', arg_supplier_id);
405: dbms_sql.bind_variable(l_sql_p,'source_org_id', arg_source_org_id);
406: dbms_sql.bind_variable(l_sql_p,'source_subinv', arg_source_subinv);
407: dbms_sql.bind_variable(l_sql_p,'line_id', arg_line_id);
408:
409: l_sql_rows := dbms_sql.execute(l_sql_p);
402: dbms_sql.bind_variable(l_sql_p,'sub_to', arg_subinv_to);
403: dbms_sql.bind_variable(l_sql_p,'source', arg_source_type);
404: dbms_sql.bind_variable(l_sql_p,'supplier_id', arg_supplier_id);
405: dbms_sql.bind_variable(l_sql_p,'source_org_id', arg_source_org_id);
406: dbms_sql.bind_variable(l_sql_p,'source_subinv', arg_source_subinv);
407: dbms_sql.bind_variable(l_sql_p,'line_id', arg_line_id);
408:
409: l_sql_rows := dbms_sql.execute(l_sql_p);
410: LOOP
403: dbms_sql.bind_variable(l_sql_p,'source', arg_source_type);
404: dbms_sql.bind_variable(l_sql_p,'supplier_id', arg_supplier_id);
405: dbms_sql.bind_variable(l_sql_p,'source_org_id', arg_source_org_id);
406: dbms_sql.bind_variable(l_sql_p,'source_subinv', arg_source_subinv);
407: dbms_sql.bind_variable(l_sql_p,'line_id', arg_line_id);
408:
409: l_sql_rows := dbms_sql.execute(l_sql_p);
410: LOOP
411: if ( dbms_sql.fetch_rows(l_sql_p) > 0 ) then
405: dbms_sql.bind_variable(l_sql_p,'source_org_id', arg_source_org_id);
406: dbms_sql.bind_variable(l_sql_p,'source_subinv', arg_source_subinv);
407: dbms_sql.bind_variable(l_sql_p,'line_id', arg_line_id);
408:
409: l_sql_rows := dbms_sql.execute(l_sql_p);
410: LOOP
411: if ( dbms_sql.fetch_rows(l_sql_p) > 0 ) then
412: dbms_sql.column_value(l_sql_p,1,ld_pull_seq_id);
413: dbms_sql.column_value(l_sql_p,2,ld_item_id);
407: dbms_sql.bind_variable(l_sql_p,'line_id', arg_line_id);
408:
409: l_sql_rows := dbms_sql.execute(l_sql_p);
410: LOOP
411: if ( dbms_sql.fetch_rows(l_sql_p) > 0 ) then
412: dbms_sql.column_value(l_sql_p,1,ld_pull_seq_id);
413: dbms_sql.column_value(l_sql_p,2,ld_item_id);
414: dbms_sql.column_value(l_sql_p,3,ld_subinv);
415: dbms_sql.column_value(l_sql_p,4,ld_loc_id);
408:
409: l_sql_rows := dbms_sql.execute(l_sql_p);
410: LOOP
411: if ( dbms_sql.fetch_rows(l_sql_p) > 0 ) then
412: dbms_sql.column_value(l_sql_p,1,ld_pull_seq_id);
413: dbms_sql.column_value(l_sql_p,2,ld_item_id);
414: dbms_sql.column_value(l_sql_p,3,ld_subinv);
415: dbms_sql.column_value(l_sql_p,4,ld_loc_id);
416:
409: l_sql_rows := dbms_sql.execute(l_sql_p);
410: LOOP
411: if ( dbms_sql.fetch_rows(l_sql_p) > 0 ) then
412: dbms_sql.column_value(l_sql_p,1,ld_pull_seq_id);
413: dbms_sql.column_value(l_sql_p,2,ld_item_id);
414: dbms_sql.column_value(l_sql_p,3,ld_subinv);
415: dbms_sql.column_value(l_sql_p,4,ld_loc_id);
416:
417: FOR Purge_Rec in Cur_Kanban(arg_source_type,ld_item_id,ld_subinv) LOOP
410: LOOP
411: if ( dbms_sql.fetch_rows(l_sql_p) > 0 ) then
412: dbms_sql.column_value(l_sql_p,1,ld_pull_seq_id);
413: dbms_sql.column_value(l_sql_p,2,ld_item_id);
414: dbms_sql.column_value(l_sql_p,3,ld_subinv);
415: dbms_sql.column_value(l_sql_p,4,ld_loc_id);
416:
417: FOR Purge_Rec in Cur_Kanban(arg_source_type,ld_item_id,ld_subinv) LOOP
418:
411: if ( dbms_sql.fetch_rows(l_sql_p) > 0 ) then
412: dbms_sql.column_value(l_sql_p,1,ld_pull_seq_id);
413: dbms_sql.column_value(l_sql_p,2,ld_item_id);
414: dbms_sql.column_value(l_sql_p,3,ld_subinv);
415: dbms_sql.column_value(l_sql_p,4,ld_loc_id);
416:
417: FOR Purge_Rec in Cur_Kanban(arg_source_type,ld_item_id,ld_subinv) LOOP
418:
419: Purge_Kanban_Cards(purge_rec.pull_sequence_id,
452: END LOOP;
453:
454: else
455: -- No more rows in the cursor
456: dbms_sql.close_cursor(l_sql_p);
457: EXIT;
458: end if;
459: END LOOP;
460:
468: retcode := G_ERROR;
469: if (errbuf = NULL) then
470: errbuf := SUBSTR(SQLERRM, 1, 500);
471: end if;
472: dbms_sql.close_cursor(l_sql_p);
473: ROLLBACK;
474: MRP_UTIL.MRP_LOG('Error at '||l_stmt_num|| ' in Purge_Kanban_Card');
475: End Purge_Kanban;
476: END FLM_KANBAN_PURGE;