API to Create Item Cost in OPM

GMF_ITEMCOST_PUB.CREATE_ITEM_COST API is used to create item cost in Oracle Process Manufacturing.
This API creates a new item cost in the Cost Details table. [ Table = CM_CMPT_DTL]


The GMF_ITEMCOST_PUB package defines and uses the following custom PL/SQL
Record Types and INDEX BY table Types:
TYPE header_rec_type IS RECORD
(
period_id cm_cmpt_dtl.period_id%TYPE,
calendar_code cm_cldr_hdr_b.calendar_code%TYPE,
period_code cm_cldr_dtl.period_code%TYPE,
cost_type_id cm_cmpt_dtl.cost_type_id%TYPE,
cost_mthd_code cm_mthd_mst.cost_mthd_code%TYPE,
organization_id cm_cmpt_dtl.organization_id%TYPE,
organization_code mtl_parameters.organization_code%TYPE,
inventory_item_id cm_cmpt_dtl.inventory_item_id%TYPE,
item_number mtl_item_flexfields.item_number%TYPE,
user_name fnd_user.user_name%TYPE
);


TYPE this_level_dtl_rec_type IS RECORD
(
cmpntcost_id NUMBER,
cost_cmpntcls_id NUMBER,
cost_cmpntcls_code cm_cmpt_mst.cost_cmpntcls_code%TYPE,
cost_analysis_code cm_cmpt_dtl.cost_analysis_code%TYPE,
cmpnt_cost NUMBER,
burden_ind NUMBER,
total_qty NUMBER,
costcalc_orig NUMBER,
rmcalc_type NUMBER,
delete_mark NUMBER,
attribute1 cm_cmpt_dtl.attribute1%TYPE,
attribute2 cm_cmpt_dtl.attribute2%TYPE,
attribute3 cm_cmpt_dtl.attribute3%TYPE,
attribute4 cm_cmpt_dtl.attribute4%TYPE,
attribute5 cm_cmpt_dtl.attribute5%TYPE,
attribute6 cm_cmpt_dtl.attribute6%TYPE,
attribute7 cm_cmpt_dtl.attribute7%TYPE,
attribute8 cm_cmpt_dtl.attribute8%TYPE,
attribute9 cm_cmpt_dtl.attribute9%TYPE,
attribute10 cm_cmpt_dtl.attribute10%TYPE,
attribute11 cm_cmpt_dtl.attribute11%TYPE,
attribute12 cm_cmpt_dtl.attribute12%TYPE,
attribute13 cm_cmpt_dtl.attribute13%TYPE,
attribute14 cm_cmpt_dtl.attribute14%TYPE,
attribute15 cm_cmpt_dtl.attribute15%TYPE,
attribute16 cm_cmpt_dtl.attribute16%TYPE,
attribute17 cm_cmpt_dtl.attribute17%TYPE,
attribute18 cm_cmpt_dtl.attribute18%TYPE,
attribute19 cm_cmpt_dtl.attribute19%TYPE,
attribute20 cm_cmpt_dtl.attribute20%TYPE,
attribute21 cm_cmpt_dtl.attribute21%TYPE,
attribute22 cm_cmpt_dtl.attribute22%TYPE,
attribute23 cm_cmpt_dtl.attribute23%TYPE,
attribute24 cm_cmpt_dtl.attribute24%TYPE,
attribute25 cm_cmpt_dtl.attribute25%TYPE,
attribute26 cm_cmpt_dtl.attribute26%TYPE,
attribute27 cm_cmpt_dtl.attribute27%TYPE,
attribute28 cm_cmpt_dtl.attribute28%TYPE,
attribute29 cm_cmpt_dtl.attribute29%TYPE,
attribute30 cm_cmpt_dtl.attribute30%TYPE,
attribute_category cm_cmpt_dtl.attribute_category%TYPE
);

TYPE this_level_dtl_tbl_type IS TABLE OF this_level_dtl_rec_type INDEX
BY BINARY_INTEGER;
TYPE lower_level_dtl_rec_type IS RECORD
(
cmpntcost_id NUMBER,
cost_cmpntcls_id NUMBER,
cost_cmpntcls_code cm_cmpt_mst.cost_cmpntcls_code%TYPE,
cost_analysis_code cm_cmpt_dtl.cost_analysis_code%TYPE,
cmpnt_cost NUMBER,
delete_mark NUMBER
);
TYPE lower_level_dtl_tbl_type IS TABLE OF lower_level_dtl_rec_type
INDEX BY BINARY_INTEGER;


TYPE costcmpnt_ids_rec_type IS RECORD
(
cost_cmpntcls_id NUMBER,
cost_analysis_code cm_cmpt_dtl.cost_analysis_code%TYPE,
cost_level NUMBER,
cmpntcost_id NUMBER
);

TYPE costcmpnt_ids_tbl_type IS TABLE OF costcmpnt_ids_rec_type INDEX
BY BINARY_INTEGER;

PL - SQL Code for API:-


ALTER SESSION SET NLS_LANGUAGE=AMERICAN;

DECLARE
   L_STATUS          VARCHAR2 (11);
   L_RETURN_STATUS   VARCHAR2 (11)               := FND_API.G_RET_STS_SUCCESS;
   L_COUNT           NUMBER (10);
   L_RECORD_COUNT    NUMBER (10)                               := 0;
   L_LOOP_CNT        NUMBER (10)                               := 0;
   L_DUMMY_CNT       NUMBER (10)                               := 0;
   L_DATA            VARCHAR2 (3000);
   L_MSG_INDEX_OUT   NUMBER;
   L_HEADER_REC      GMF_ITEMCOST_PUB.HEADER_REC_TYPE;
   L_THIS_LVL_TBL    GMF_ITEMCOST_PUB.THIS_LEVEL_DTL_TBL_TYPE;
   L_LOWER_LVL_TBL   GMF_ITEMCOST_PUB.LOWER_LEVEL_DTL_TBL_TYPE;
   L_COSTCMPNT_IDS   GMF_ITEMCOST_PUB.COSTCMPNT_IDS_TBL_TYPE;
BEGIN
   FND_GLOBAL.APPS_INITIALIZE (USER_ID           => 1110,
                               RESP_ID           => 50687,
                               RESP_APPL_ID      => 555
                              );
                             
   L_HEADER_REC.PERIOD_ID           := 141;
   L_HEADER_REC.CALENDAR_CODE       := 'CST_CAL';
   L_HEADER_REC.PERIOD_CODE         := 'JUN 2012'; --NULL;
   L_HEADER_REC.COST_TYPE_ID        := 1000;
   L_HEADER_REC.COST_MTHD_CODE      := 'PMAC';   -- NULL;
   L_HEADER_REC.ORGANIZATION_ID     := 229;
   L_HEADER_REC.ORGANIZATION_CODE   := 'R02'; --NULL;
   L_HEADER_REC.INVENTORY_ITEM_ID   := 2005;
--L_HEADER_REC.ITEM_NUMBER          := NULL;
   L_HEADER_REC.USER_NAME           := 'USER1';
 
   L_THIS_LVL_TBL (1).CMPNTCOST_ID          := 21188;
   L_THIS_LVL_TBL (1).COST_CMPNTCLS_ID      := 1;
   L_THIS_LVL_TBL (1).COST_CMPNTCLS_CODE    := 'MATERIAL';
   L_THIS_LVL_TBL (1).COST_ANALYSIS_CODE    := 'DIR';
   L_THIS_LVL_TBL (1).CMPNT_COST            := 60;
   L_THIS_LVL_TBL (1).BURDEN_IND            := 0;
   L_THIS_LVL_TBL (1).TOTAL_QTY             := 0;
   L_THIS_LVL_TBL (1).COSTCALC_ORIG         := 1;
   L_THIS_LVL_TBL (1).RMCALC_TYPE           := 1;
   L_THIS_LVL_TBL (1).DELETE_MARK           := 0;
 
   DBMS_OUTPUT.PUT_LINE ('Start');
   GMF_ITEMCOST_PUB.CREATE_ITEM_COST
                                    (P_API_VERSION              => 3.0,
                                     P_INIT_MSG_LIST            => FND_API.G_FALSE,
                                     P_COMMIT                   => FND_API.G_FALSE,
                                     X_RETURN_STATUS            => L_STATUS,
                                     X_MSG_COUNT                => L_COUNT,
                                     X_MSG_DATA                 => L_DATA,
                                     P_HEADER_REC               => L_HEADER_REC,
                                     P_THIS_LEVEL_DTL_TBL       => L_THIS_LVL_TBL,
                                     P_LOWER_LEVEL_DTL_TBL      => L_LOWER_LVL_TBL,
                                     X_COSTCMPNT_IDS            => L_COSTCMPNT_IDS
                                    );
   DBMS_OUTPUT.PUT_LINE ('Status:' || L_STATUS);
   DBMS_OUTPUT.PUT_LINE ('Debug:' || L_COUNT);

   IF L_STATUS = 'S'
   THEN
      COMMIT;
      DBMS_OUTPUT.PUT_LINE ('success!!');
   ELSE
      IF L_COUNT = 1
      THEN
         DBMS_OUTPUT.PUT_LINE ('Error 11111 ' || L_DATA);
      ELSE
         DBMS_OUTPUT.PUT_LINE (   'status: '
                               || L_STATUS
                               || ' Error Count '
                               || L_COUNT
                              );

         FOR I IN 1 .. L_COUNT
         LOOP
            FND_MSG_PUB.GET (P_MSG_INDEX          => I,
                             P_DATA               => L_DATA,
                             P_ENCODED            => FND_API.G_FALSE,
                             P_MSG_INDEX_OUT      => L_MSG_INDEX_OUT
                            );
            DBMS_OUTPUT.PUT_LINE ('Error 2222: ' || SUBSTR (L_DATA, 1, 255));
         END LOOP;
      END IF;
   END IF;

   DBMS_OUTPUT.PUT_LINE ('end of procedure');
END;

API for Bank Account Creation in Oracle Apps

CE_BANK_PUB.CREATE_BANK_ACCT API is used to create a bank account for the branch party id passed as an input parameter. The API uses the BANKACCT_REC_TYPE record to pass the input values. On successful creation of bank account, the API returns the bank account id along with the information /error messages. The API returns a null bank account id if the bank branch is not created.



DECLARE

   p_init_msg_list   VARCHAR2 (200);
   p_acct_rec        apps.ce_bank_pub.bankacct_rec_type;
   x_acct_id         NUMBER;
   x_return_status   VARCHAR2 (200);
   x_msg_count       NUMBER;
   x_msg_data        VARCHAR2 (200);
   p_count           NUMBER;

BEGIN
   p_init_msg_list := NULL;
   -- HZ_PARTIES.PARTY_ID BANK BRANCH
   p_acct_rec.branch_id := 8056;
   -- HZ_PARTIES.PARTY_ID BANK
   p_acct_rec.bank_id := 8042;
   -- HZ_PARTIES.PARTY_ID ORGANIZATION
   p_acct_rec.account_owner_org_id := 23273;
   -- HZ_PARTIES.PARTY_ID Person related to ABOVE ORGANIZATION
   p_acct_rec.account_owner_party_id := 2041;

   p_acct_rec.account_classification := 'INTERNAL';
   p_acct_rec.bank_account_name := 'Test Bank Accunt';
   p_acct_rec.bank_account_num := 14256789;
   p_acct_rec.currency := 'INR';
   p_acct_rec.start_date := SYSDATE;
   p_acct_rec.end_date := NULL;

   CE_BANK_PUB.CREATE_BANK_ACCT
                  (p_init_msg_list      => p_init_msg_list,
                   p_acct_rec           => p_acct_rec,
                   x_acct_id            => x_acct_id,
                   x_return_status      => x_return_status,
                   x_msg_count          => x_msg_count,
                   x_msg_data           => x_msg_data
                  );
                       
   DBMS_OUTPUT.put_line ('X_ACCT_ID = ' || x_acct_id);
   DBMS_OUTPUT.put_line ('X_RETURN_STATUS = ' || x_return_status);
   DBMS_OUTPUT.put_line ('X_MSG_COUNT = ' || x_msg_count);
   DBMS_OUTPUT.put_line ('X_MSG_DATA = ' || x_msg_data);

   IF x_msg_count = 1
   THEN
      DBMS_OUTPUT.put_line ('x_msg_data ' || x_msg_data);
   ELSIF x_msg_count > 1
   THEN
      LOOP
         p_count := p_count + 1;
         x_msg_data := fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);

         IF x_msg_data IS NULL
         THEN
            EXIT;
         END IF;

         DBMS_OUTPUT.put_line ('Message' || p_count || ' ---' || x_msg_data);
      END LOOP;
   END IF;
END;