Orabr

COMO ESTUDAR ORACLE ERP CLOUD ON-LINE?

Estudar por frentes Todas as frentes, módulos e sub módulos

Leia mais

Case para desenvolvedores Oracle V.1.1

O que temos na versão 1.1 do case para desenvolvedores Oracle? Máquina VirtualRAM: 2.0 Gb Ou 2048 MbCPU: 2 CoreHD: 600 GB Sistema OperacionalCentos 7 X86-64Desktop…

Leia mais

Posts rotativos

Leia mais

INSTANCE ORACLE EBS R12.1.3 [LOCALIZAÇÕES BRASIL] PARA DOWNLOAD

Imagem Oracle EBS R12.1.3, VISION com localizações brasileiras. Sobre a Instância. Esta instância foi ajustada para rodar em computadores com baixa configuração. Isso permite que mais pessoas…

Leia mais

BUSCAR VAGAS ORACLE PELO MUNDO

PAÍS CONTATO SITE MUNDO NA http://www.talentinternational.com MUNDO NA https://www.oraconnect.net MUNDO NA https://www.monster.com MUNDO NA https://www.indeed.com MUNDO George.Paton-Philip@oraclecontractors.com http://www.oraclecontractors.com AUSTRÁLIA NA http://au.hudson.com/ AUSTRÁLIA Recutador: Shannon Orr…

Leia mais

Oracle – AIM (Oracle Applications – Functional) Download

A Oracle Application Implementation Methodology (AIM) é uma abordagem comprovada para implementação de aplicativos Oracle em domínios comerciais. Essa abordagem foi desenvolvida pela Oracle em…

Leia mais

Case para desenvolvedores Oracle V.1.0

O que temos na versão 1.0 do case para desenvolvedores Oracle? Máquina Virtual RAM: 2.0 Gb Ou 2048 Mb CPU: 1 Core HD: 140 GB Sistema…

Leia mais

Reenvio de nova senha Orabr.com

Reenvio de nova senha Orabr.com Caro membro do Orabr, Tivemos recentemente alguns problemas com o reset de senha de alguns usuários. Gostaríamos de informar que o problema…

Leia mais

Oracle VISION DEMO – ON-LINE

http://diligentsolutions.net/?q=pages/OracleInstances/oracle-ebs-r1224-vision-demo

Leia mais

Aprendizagem técnico OTN em português, sessões gratuitas de vídeo.

Una-se a esta experiência de aprendizagem prática gratuita com a Oracle e especialistas da comunidade Oracle Technology Network convida você a participar da nova Cúpula…

Leia mais

ORACLE EBS ON-LINE – PARA ESTUDOS – FREE INSTANCE R12 OU R11

Instance online. Oracle EBS R12.1.3 para estudos e testes. Solicitar acesso http://www.biztech.com

Leia mais

[PTPGROUP] Hiring OBIEE

HIRING!! #LATAM/REMOTE OBIEE Details OBIEE 5years + Design and support of Data Integrations and within BI tools and Apps Informatica ODI Relational Database Modeling…

Leia mais

[PTPGROUP] Vaga Oracle Fusion OM – PJ – zona sul de São Paulo, SP

Prezadas(Os), Compartilho vaga: Oracle – ORACLE FUSION FUNCIONAL OM • Mandatórios: Oracle Fusion OM, Apoio implementação Apoio desenvolvimento / correção de relatórios. Acompanhamento de…

Leia mais

Assunto: Re: [PTPGROUP] DDA Itaú

Boa tarde, Lucas! Poderia me enviar os números dos patches? Grato! Enviado do Yahoo Mail no Android <div>Em qui, 18 18e jul 18e 2019…

Leia mais

[PTPGROUP] Oportunidade Bertini

Boa tarde estamos com uma oportunidade urgente para: ORACLE DATA LOADER PROFESSIONAL Atuação no RJ e SP. Aproximadamente 1 mês de alocação…

Leia mais

[PTPGROUP] DDA Itaú

Oi Lucas, Voce teria os numeros desses patches, por favor? Dagmar Izar Consultora Oracle EBS Financials * dagmar_izar@yahoo.com.br( +55 11 99909-7109) Skype: dagmar.izar Em…

Leia mais

[PTPGROUP] DDA Itaú

Boa tarde Marcos, Resolvi esse problema aplicando os últimos patches de localização para DDA e cadastrando agência/conta conforme arquivo (dados header Itaú 0000, etc).…

Leia mais

[PTPGROUP] Consultor Oracle AR

Augustina, bom dia! Estou interessado na vaga postada no ptpgroup em 17/07 e gostaria de ter mais informações sobre o projeto. Segue meu Currículo…

Leia mais

DDA Itaú

Boa noite! Alguém poderia me enviar mapeamento do DDA para que eu posso validar o mapeamento que fiz. Estou enfrentando um problema com a…

Leia mais

HIRING! Consultor Oracle AR

Para um projeto de curto prazo de 1 ou 2 semanas, a ITC contratará um consultor com conhecimento em AR. Interessado por favor envie…

Leia mais

Oportunidade BERTINI DO BRASIL

;word-spacing:0px;”>Consultor ERP Financials ( EBS ou Cloud) Formação em Computação ou Matemática aplicada a Computação. Sólida experiencia em PL/SQL para ERP Conhecimento de BI Publisher e…

Leia mais

Oportunidade EBS Financials Chile

Boa tarde pessoal, Gostaria de fazer um REPOST da vaga abaixo, que ainda está em aberto. Com a diferença que não é mais…

Leia mais

R12 – QUERY para encontrar detalhes da PO, projeto, pedidos / recebimentos / ordem de movimento / consumo

SELECT (SELECT DISTINCT organization_name
          FROM org_organization_definitions
         WHERE organization_id = pa.carrying_out_organization_id) org_name
      ,(SELECT DISTINCT organization_code
          FROM org_organization_definitions
         WHERE organization_id = pa.carrying_out_organization_id) org_code
      ,pa.project_id
      ,pa.org_id
      ,pa.NAME
      ,pa.long_name
      ,pa.segment1
      ,pa.project_status_code
      ,pab.burdened_cost budget_cost
      ,pa.created_by
      ,pa.project_type
      ,pa.start_date
      ,pa.completion_date
      ,pa.closed_date
      ,pa.carrying_out_organization_id
      ,NVL((SELECT DISTINCT ppf.first_name || ' ' || ppf.last_name
             FROM pa_project_players p
                 ,per_all_people_f   ppf
            WHERE ppf.person_id = p.person_id
              AND project_role_type = 'PROJECT MANAGER'
              AND p.project_id = pa.project_id), 'Not Defined') key_member
      ,pt.task_id
      ,pt.task_number
      ,pt.attribute1 task_status
      ,pt.task_name
      ,pt.description
      ,pab.budget_version_id
      ,trunc(pab.cur_base_date) approval_date
      ,(SELECT SUM(burdened_cost)
          FROM pafv_budget_lines
         WHERE task_id = pt.task_id
           AND budget_version_id = pab.budget_version_id
         GROUP BY task_id) task_budget
      ,pt.top_task_id
      ,pt.wbs_level
      ,pt.start_date
      ,pt.completion_date
      ,pod.DESTINATION_TYPE_CODE
      ,
       -- pod.PO_DISTRIBUTION_ID,
       (SELECT item_id
          FROM po_lines_all
         WHERE po_line_id = pod.po_line_id) item
      ,(SELECT description
          FROM mtl_system_items_kfv k
         WHERE inventory_item_id =
               (SELECT item_id
                  FROM po_lines_all
                 WHERE po_line_id = pod.po_line_id)
           AND organization_id = pa.carrying_out_organization_id) item_desc
      ,(SELECT concatenated_segments
          FROM mtl_system_items_kfv k
         WHERE inventory_item_id =
               (SELECT item_id
                  FROM po_lines_all
                 WHERE po_line_id = pod.po_line_id)
           AND organization_id = pa.carrying_out_organization_id) item_code
      ,(SELECT pha.segment1
          FROM po_headers_all pha
         WHERE pha.po_header_id = pod.po_header_id) po_num
      ,(SELECT pv.vendor_name
          FROM po_headers_all pha
              ,po_vendors     pv
         WHERE pv.vendor_id = pha.vendor_id
           AND pha.po_header_id = pod.po_header_id) vendor_name
      ,(SELECT pha.currency_code
          FROM po_headers_all pha
         WHERE pha.po_header_id = pod.po_header_id) Currency_Code
      ,(SELECT trunc(pha.creation_date)
          FROM po_headers_all pha
         WHERE pha.po_header_id = pod.po_header_id) po_date
      ,(SELECT quantity
          FROM po_lines_all
         WHERE po_line_id = pod.po_line_id) PO_QTY
      ,(SELECT unit_price
          FROM po_lines_all
         WHERE po_line_id = pod.po_line_id) PO_unit_price
      ,(SELECT (pl.unit_price * pl.quantity * nvl(ph.rate, 1))
          FROM po_lines_all   pl
              ,po_headers_all ph
         WHERE ph.po_header_id = pl.po_header_id
           AND pl.po_line_id = pod.po_line_id) PO_commit_cost
      ,(SELECT DISTINCT receipt_num
          FROM rcv_shipment_headers rsh
              ,rcv_transactions     rcv
              ,po_lines_all         pla
         WHERE rsh.shipment_header_id = rcv.shipment_header_id
           AND rcv.po_line_id = pla.po_line_id
           AND PO_DISTRIBUTION_ID = pod.PO_DISTRIBUTION_ID
           AND transaction_id = rcv1.transaction_id) rec_num
      ,(SELECT DISTINCT trunc(transaction_date)
          FROM rcv_transactions rcv
         WHERE PO_DISTRIBUTION_ID = pod.PO_DISTRIBUTION_ID
           AND transaction_type = 'DELIVER'
           AND rownum = 1) Rec_Date
      ,(SELECT SUM((decode(transaction_type, 'DELIVER', quantity,
                           'RETURN TO VENDOR', (quantity * (-1)))))
          FROM rcv_transactions rcv
         WHERE PO_DISTRIBUTION_ID = pod.PO_DISTRIBUTION_ID
           AND shipment_line_id = rcv1.shipment_line_id
           AND transaction_type IN ('DELIVER', 'RETURN TO VENDOR')) rec_qty
      ,(SELECT DISTINCT po_unit_price
          FROM rcv_transactions rcv
         WHERE PO_DISTRIBUTION_ID = pod.PO_DISTRIBUTION_ID
           AND transaction_type = 'DELIVER') Rec_Price
      ,((SELECT SUM((decode(transaction_type, 'DELIVER', quantity,
                            'RETURN TO VENDOR', (quantity * (-1)))))
           FROM rcv_transactions rcv
          WHERE PO_DISTRIBUTION_ID = pod.PO_DISTRIBUTION_ID
            AND shipment_line_id = rcv1.shipment_line_id
            AND transaction_type IN ('DELIVER', 'RETURN TO VENDOR')) *
       (SELECT DISTINCT po_unit_price
           FROM rcv_transactions rcv
          WHERE PO_DISTRIBUTION_ID = pod.PO_DISTRIBUTION_ID
            AND transaction_type = 'DELIVER')) Rec_Val
      ,NULL transfered_project_from_other
      ,NULL transfered_qty_from_other
      ,NULL transfered_date_from_other
      ,NULL transfered_cost_from_other
      ,NULL transfer_project_to_other
      ,NULL transfer_qty_to_other
      ,NULL transfer_cost_to_other
      ,NULL transfer_date_to_other
      ,NULL issued_qty
      ,NULL issued_date
      ,NULL issued_cost
      ,NULL issued_ref_no
      ,DECODE(pod.DESTINATION_TYPE_CODE, 'EXPENSE',
              ((SELECT SUM((decode(transaction_type, 'DELIVER', quantity,
                                    'RETURN TO VENDOR', (quantity * (-1)))))
                   FROM rcv_transactions rcv
                  WHERE PO_DISTRIBUTION_ID = pod.PO_DISTRIBUTION_ID
                    AND transaction_type IN ('DELIVER', 'RETURN TO VENDOR')) *
               (SELECT DISTINCT po_unit_price
                   FROM rcv_transactions rcv
                  WHERE PO_DISTRIBUTION_ID = pod.PO_DISTRIBUTION_ID
                    AND transaction_type = 'DELIVER'))) expense_loaded_to_project
      ,NULL to_project_id
      ,NULL transaction_quantity
      ,NULL transaction_type
      ,NULL subinventory_code
      ,NULL locator_id
      ,NULL location
      ,(SELECT DISTINCT uom_code
          FROM mtl_units_of_measure_tl uo
              ,po_lines_all            pl
         WHERE pl.UNIT_MEAS_LOOKUP_CODE = uo.unit_of_measure
           AND po_line_id = pod.po_line_id) transaction_uom
      ,(SELECT pha.creation_date
          FROM po_headers_all pha
         WHERE pha.po_header_id = pod.po_header_id) trans_date
      ,NULL per_unit_cost
  FROM pa_projects_all            pa
      ,pa_tasks                   pt
      ,pa_budget_versions_draft_v pab
      ,pa_budget_types            ty
      ,po_distributions_all       pod
      ,rcv_transactions           rcv1
 WHERE pa.project_id = pt.project_id
   AND pa.project_id = pab.project_id
   AND ty.budget_type_code = pab.budget_type_code
   AND pod.task_id(+) = pt.task_id
   AND pod.task_id = rcv1.task_id(+)
   AND pod.po_distribution_id = rcv1.po_distribution_id(+)
   AND pa.org_id = :p_org_id
   AND pa.segment1 = :p_from_segment
/*  AND pa.project_status_code = NVL (:p_status, pa.project_status_code)
AND pa.segment1 BETWEEN NVL (:p_from_segment, pa.segment1)
                    AND NVL (:p_to_segment, pa.segment1)
AND TRUNC (pa.start_date) BETWEEN NVL
                                     (TRUNC (TO_DATE (:p_from_start_date,
                                                      'DD-MON-RRRR'
                                                     )
                                            ),
                                      TRUNC (pa.start_date)
                                     )
                              AND NVL (TRUNC (TO_DATE (:p_to_start_date,
                                                       'DD-MON-RRRR'
                                                      )
                                             ),
                                       TRUNC (pa.start_date)
                                      )
AND pab.burdened_cost > = NVL (:p_cost, pab.burdened_cost)     */
UNION
SELECT (SELECT DISTINCT organization_name
          FROM org_organization_definitions
         WHERE organization_id = pa.carrying_out_organization_id) org_name
      ,(SELECT DISTINCT organization_code
          FROM org_organization_definitions
         WHERE organization_id = pa.carrying_out_organization_id) org_code
      ,pa.project_id
      ,pa.org_id
      ,pa.NAME
      ,pa.long_name
      ,pa.segment1
      ,pa.project_status_code
      ,pab.burdened_cost budget_cost
      ,pa.created_by
      ,pa.project_type
      ,pa.start_date
      ,pa.completion_date
      ,pa.closed_date
      ,pa.carrying_out_organization_id
      ,NVL((SELECT DISTINCT ppf.first_name || ' ' || ppf.last_name
             FROM pa_project_players p
                 ,per_all_people_f   ppf
            WHERE ppf.person_id = p.person_id
              AND project_role_type = 'PROJECT MANAGER'
              AND p.project_id = pa.project_id), 'Not Defined') key_member
      ,pt.task_id
      ,pt.task_number
      ,pt.attribute1 task_status
      ,pt.task_name
      ,pt.description
      ,pab.budget_version_id
      ,trunc(pab.cur_base_date) approval_date
      ,(SELECT SUM(burdened_cost)
          FROM pafv_budget_lines
         WHERE task_id = pt.task_id
           AND budget_version_id = pab.budget_version_id
         GROUP BY task_id) task_budget
      ,pt.top_task_id
      ,pt.wbs_level
      ,pt.start_date
      ,pt.completion_date
      ,NULL destination_type_code
      ,mmt.inventory_item_id item
      ,(SELECT description
          FROM mtl_system_items_kfv k
         WHERE inventory_item_id = mmt.inventory_item_id
           AND organization_id = pa.carrying_out_organization_id) item_desc
      ,(SELECT concatenated_segments
          FROM mtl_system_items_kfv k
         WHERE inventory_item_id = mmt.inventory_item_id
           AND organization_id = pa.carrying_out_organization_id) item_code
      ,NULL po_num
      ,NULL vendor_name
      ,NULL Currency_Code
      ,NULL po_date
      ,NULL po_qty
      ,NULL po_unit_price
      ,NULL po_commit_cost
      ,NULL rec_num
      ,NULL rec_date
      ,NULL rec_qty
      ,NULL rec_price
      ,NULL rec_val
      ,CASE
         WHEN mmt.transaction_quantity > 0 THEN
          decode(mmt.transaction_type_id, 66,
                 (SELECT DISTINCT segment1
                     FROM pa_projects_all
                    WHERE project_id = mmt.to_project_id), 67,
                 (SELECT DISTINCT segment1
                     FROM pa_projects_all
                    WHERE project_id = mmt.to_project_id), 68,
                 (SELECT DISTINCT segment1
                     FROM pa_projects_all
                    WHERE project_id = mmt.to_project_id))
       END AS transfered_project_from_other
      ,CASE
         WHEN mmt.transaction_quantity > 0 THEN
          DECODE(mmt.transaction_type_id, 66, mmt.transaction_quantity, 67,
                 mmt.transaction_quantity, 68, mmt.transaction_quantity)
       END AS transfered_QTY_from_other
      ,CASE
         WHEN mmt.transaction_quantity > 0 THEN
          DECODE(mmt.transaction_type_id, 66, TRUNC(mmt.transaction_date), 67,
                 TRUNC(mmt.transaction_date), 68, TRUNC(mmt.transaction_date))
       END AS transfered_DATE_from_other
      ,CASE
         WHEN mmt.transaction_quantity > 0 THEN
          DECODE(mmt.transaction_type_id, 66,
                 (mmt.transaction_quantity * mmt.actual_cost), 67,
                 (mmt.transaction_quantity * mmt.actual_cost), 68,
                 (mmt.transaction_quantity * mmt.actual_cost))
       END AS transfered_cost_from_other
      ,CASE
         WHEN mmt.transaction_quantity < 0 THEN
          decode(mmt.transaction_type_id, 66,
                 (SELECT DISTINCT segment1
                     FROM pa_projects_all
                    WHERE project_id = mmt.to_project_id), 67,
                 (SELECT DISTINCT segment1
                     FROM pa_projects_all
                    WHERE project_id = mmt.to_project_id), 68,
                 (SELECT DISTINCT segment1
                     FROM pa_projects_all
                    WHERE project_id = mmt.to_project_id))
       END AS transfer_project_to_other
      ,CASE
         WHEN mmt.transaction_quantity < 0 THEN
          DECODE(mmt.transaction_type_id, 66, mmt.transaction_quantity, 67,
                 mmt.transaction_quantity, 68, mmt.transaction_quantity)
       END AS transfer_qty_to_other
      ,CASE
         WHEN mmt.transaction_quantity < 0 THEN
          DECODE(mmt.transaction_type_id, 66,
                 (mmt.transaction_quantity * mmt.actual_cost), 67,
                 (mmt.transaction_quantity * mmt.actual_cost), 68,
                 (mmt.transaction_quantity * mmt.actual_cost))
       END AS transfer_cost_to_other
      ,CASE
         WHEN mmt.transaction_quantity < 0 THEN
          DECODE(mmt.transaction_type_id, 66, TRUNC(mmt.transaction_date), 67,
                 TRUNC(mmt.transaction_date), 68, TRUNC(mmt.transaction_date))
       END AS transfer_DATE_to_other
      ,DECODE(mmt.transaction_type_id, 120, mmt.transaction_quantity, 35,
              mmt.transaction_quantity, 63, mmt.transaction_quantity) issued_qty
      ,DECODE(mmt.transaction_type_id, 120, TRUNC(mmt.transaction_date), 35,
              TRUNC(mmt.transaction_date), 63, TRUNC(mmt.transaction_date)) issued_date
      ,DECODE(mmt.transaction_type_id, 120,
              (mmt.transaction_quantity * mmt.actual_cost * (-1)), 35,
              (mmt.transaction_quantity * mmt.actual_cost * (-1)), 63,
              (mmt.transaction_quantity * mmt.actual_cost * (-1))) issued_cost
      ,DECODE(mmt.transaction_type_id, 120,
              (SELECT DISTINCT request_number
                  FROM mtl_txn_request_headers
                 WHERE header_id = mmt.transaction_source_id), 63,
              (SELECT DISTINCT request_number
                  FROM mtl_txn_request_headers
                 WHERE header_id = mmt.transaction_source_id), 35,
              (SELECT DISTINCT wip_entity_name
                  FROM wip_entities
                 WHERE wip_entity_id = mmt.transaction_source_id)) Issued_Ref_No
      ,NULL expense_loaded_to_project
      ,mmt.to_project_id
      ,mmt.transaction_quantity
      ,(SELECT description
          FROM mtl_transaction_types
         WHERE transaction_type_id = mmt.transaction_type_id
           AND mmt.transaction_action_id = transaction_action_id
           AND transaction_source_type_id = mmt.transaction_source_type_id) transaction_type
      ,mmt.subinventory_code
      ,mmt.locator_id
      ,(SELECT segment1 || '.' || segment2 || '.' || segment3
          FROM mtl_item_locations
         WHERE inventory_location_id = mmt.locator_id
           AND organization_id = pa.carrying_out_organization_id) LOCATION
      ,mmt.transaction_uom
      ,mmt.transaction_date trans_date
      ,ROUND(NVL(mmt.transaction_cost, mmt.actual_cost), 2) per_unit_COST
  FROM mtl_material_transactions  mmt
      ,pa_projects_all            pa
      ,pa_tasks                   pt
      ,po_lines_all               pll
      ,pa_budget_versions_draft_v pab
      ,mtl_txn_request_lines      mtxn
      ,pa_budget_types            ty
 WHERE mmt.transaction_set_id = mtxn.transaction_header_id(+)
   AND pa.project_id = mmt.project_id(+)
   AND pt.project_id = pa.project_id
   AND pa.project_id = pab.project_id
   AND ty.budget_type_code = pab.budget_type_code
   AND pt.task_id = mmt.task_id
   AND mmt.transaction_source_id = pll.po_header_id(+)
   AND ty.plan_type = 'BUDGET'
   AND mmt.inventory_item_id = pll.item_id(+)
   AND mmt.transaction_type_id NOT IN (18, 36)
   AND pa.org_id = :p_org_id
   AND pa.segment1 = :p_from_segment
/*   AND pa.project_status_code = NVL (:p_status, pa.project_status_code)
AND pa.segment1 BETWEEN NVL (:p_from_segment, pa.segment1)
                     AND NVL (:p_to_segment, pa.segment1)
 AND TRUNC (pa.start_date) BETWEEN NVL
                                      (TRUNC (TO_DATE (:p_from_start_date,
                                                       'DD-MON-RRRR'
                                                      )
                                             ),
                                       TRUNC (pa.start_date)
                                      )
                               AND NVL (TRUNC (TO_DATE (:p_to_start_date,
                                                        'DD-MON-RRRR'
                                                       )
                                              ),
                                        TRUNC (pa.start_date)
                                       )
 AND pab.burdened_cost > = NVL (:p_cost, pab.burdened_cost)*/
 ORDER BY item
         ,trans_date

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

SELECT (SELECT DISTINCT organization_name

FROM org_organization_definitions

WHERE organization_id = pa.carrying_out_organization_id) org_name

,(SELECT DISTINCT organization_code

FROM org_organization_definitions

WHERE organization_id = pa.carrying_out_organization_id) org_code

,pa.project_id

,pa.org_id

,pa.NAME

,pa.long_name

,pa.segment1

,pa.project_status_code

,pab.burdened_cost budget_cost

,pa.created_by

,pa.project_type

,pa.start_date

,pa.completion_date

,pa.closed_date

,pa.carrying_out_organization_id

,NVL((SELECT DISTINCT ppf.first_name || ' ' || ppf.last_name

FROM pa_project_players p

,per_all_people_f ppf

WHERE ppf.person_id = p.person_id

AND project_role_type = 'PROJECT MANAGER'

AND p.project_id = pa.project_id), 'Not Defined') key_member

,pt.task_id

,pt.task_number

,pt.attribute1 task_status

,pt.task_name

,pt.description

,pab.budget_version_id

,trunc(pab.cur_base_date) approval_date

,(SELECT SUM(burdened_cost)

FROM pafv_budget_lines

WHERE task_id = pt.task_id

AND budget_version_id = pab.budget_version_id

GROUP BY task_id) task_budget

,pt.top_task_id

,pt.wbs_level

,pt.start_date

,pt.completion_date

,pod.DESTINATION_TYPE_CODE

,

-- pod.PO_DISTRIBUTION_ID,

(SELECT item_id

FROM po_lines_all

WHERE po_line_id = pod.po_line_id) item

,(SELECT description

FROM mtl_system_items_kfv k

WHERE inventory_item_id =

(SELECT item_id

FROM po_lines_all

WHERE po_line_id = pod.po_line_id)

AND organization_id = pa.carrying_out_organization_id) item_desc

,(SELECT concatenated_segments

FROM mtl_system_items_kfv k

WHERE inventory_item_id =

(SELECT item_id

FROM po_lines_all

WHERE po_line_id = pod.po_line_id)

AND organization_id = pa.carrying_out_organization_id) item_code

,(SELECT pha.segment1

FROM po_headers_all pha

WHERE pha.po_header_id = pod.po_header_id) po_num

,(SELECT pv.vendor_name

FROM po_headers_all pha

,po_vendors pv

WHERE pv.vendor_id = pha.vendor_id

AND pha.po_header_id = pod.po_header_id) vendor_name

,(SELECT pha.currency_code

FROM po_headers_all pha

WHERE pha.po_header_id = pod.po_header_id) Currency_Code

,(SELECT trunc(pha.creation_date)

FROM po_headers_all pha

WHERE pha.po_header_id = pod.po_header_id) po_date

,(SELECT quantity

FROM po_lines_all

WHERE po_line_id = pod.po_line_id) PO_QTY

,(SELECT unit_price

FROM po_lines_all

WHERE po_line_id = pod.po_line_id) PO_unit_price

,(SELECT (pl.unit_price * pl.quantity * nvl(ph.rate, 1))

FROM po_lines_all pl

,po_headers_all ph

WHERE ph.po_header_id = pl.po_header_id

AND pl.po_line_id = pod.po_line_id) PO_commit_cost

,(SELECT DISTINCT receipt_num

FROM rcv_shipment_headers rsh

,rcv_transactions rcv

,po_lines_all pla

WHERE rsh.shipment_header_id = rcv.shipment_header_id

AND rcv.po_line_id = pla.po_line_id

AND PO_DISTRIBUTION_ID = pod.PO_DISTRIBUTION_ID

AND transaction_id = rcv1.transaction_id) rec_num

,(SELECT DISTINCT trunc(transaction_date)

FROM rcv_transactions rcv

WHERE PO_DISTRIBUTION_ID = pod.PO_DISTRIBUTION_ID

AND transaction_type = 'DELIVER'

AND rownum = 1) Rec_Date

,(SELECT SUM((decode(transaction_type, 'DELIVER', quantity,

'RETURN TO VENDOR', (quantity * (-1)))))

FROM rcv_transactions rcv

WHERE PO_DISTRIBUTION_ID = pod.PO_DISTRIBUTION_ID

AND shipment_line_id = rcv1.shipment_line_id

AND transaction_type IN ('DELIVER', 'RETURN TO VENDOR')) rec_qty

,(SELECT DISTINCT po_unit_price

FROM rcv_transactions rcv

WHERE PO_DISTRIBUTION_ID = pod.PO_DISTRIBUTION_ID

AND transaction_type = 'DELIVER') Rec_Price

,((SELECT SUM((decode(transaction_type, 'DELIVER', quantity,

'RETURN TO VENDOR', (quantity * (-1)))))

FROM rcv_transactions rcv

WHERE PO_DISTRIBUTION_ID = pod.PO_DISTRIBUTION_ID

AND shipment_line_id = rcv1.shipment_line_id

AND transaction_type IN ('DELIVER', 'RETURN TO VENDOR')) *

(SELECT DISTINCT po_unit_price

FROM rcv_transactions rcv

WHERE PO_DISTRIBUTION_ID = pod.PO_DISTRIBUTION_ID

AND transaction_type = 'DELIVER')) Rec_Val

,NULL transfered_project_from_other

,NULL transfered_qty_from_other

,NULL transfered_date_from_other

,NULL transfered_cost_from_other

,NULL transfer_project_to_other

,NULL transfer_qty_to_other

,NULL transfer_cost_to_other

,NULL transfer_date_to_other

,NULL issued_qty

,NULL issued_date

,NULL issued_cost

,NULL issued_ref_no

,DECODE(pod.DESTINATION_TYPE_CODE, 'EXPENSE',

((SELECT SUM((decode(transaction_type, 'DELIVER', quantity,

'RETURN TO VENDOR', (quantity * (-1)))))

FROM rcv_transactions rcv

WHERE PO_DISTRIBUTION_ID = pod.PO_DISTRIBUTION_ID

AND transaction_type IN ('DELIVER', 'RETURN TO VENDOR')) *

(SELECT DISTINCT po_unit_price

FROM rcv_transactions rcv

WHERE PO_DISTRIBUTION_ID = pod.PO_DISTRIBUTION_ID

AND transaction_type = 'DELIVER'))) expense_loaded_to_project

,NULL to_project_id

,NULL transaction_quantity

,NULL transaction_type

,NULL subinventory_code

,NULL locator_id

,NULL location

,(SELECT DISTINCT uom_code

FROM mtl_units_of_measure_tl uo

,po_lines_all pl

WHERE pl.UNIT_MEAS_LOOKUP_CODE = uo.unit_of_measure

AND po_line_id = pod.po_line_id) transaction_uom

,(SELECT pha.creation_date

FROM po_headers_all pha

WHERE pha.po_header_id = pod.po_header_id) trans_date

,NULL per_unit_cost

FROM pa_projects_all pa

,pa_tasks pt

,pa_budget_versions_draft_v pab

,pa_budget_types ty

,po_distributions_all pod

,rcv_transactions rcv1

WHERE pa.project_id = pt.project_id

AND pa.project_id = pab.project_id

AND ty.budget_type_code = pab.budget_type_code

AND pod.task_id(+) = pt.task_id

AND pod.task_id = rcv1.task_id(+)

AND pod.po_distribution_id = rcv1.po_distribution_id(+)

AND pa.org_id = :p_org_id

AND pa.segment1 = :p_from_segment

/* AND pa.project_status_code = NVL (:p_status, pa.project_status_code)

AND pa.segment1 BETWEEN NVL (:p_from_segment, pa.segment1)

AND NVL (:p_to_segment, pa.segment1)

AND TRUNC (pa.start_date) BETWEEN NVL

(TRUNC (TO_DATE (:p_from_start_date,

'DD-MON-RRRR'

)

),

TRUNC (pa.start_date)

)

AND NVL (TRUNC (TO_DATE (:p_to_start_date,

'DD-MON-RRRR'

)

),

TRUNC (pa.start_date)

)

AND pab.burdened_cost > = NVL (:p_cost, pab.burdened_cost) */

UNION

SELECT (SELECT DISTINCT organization_name

FROM org_organization_definitions

WHERE organization_id = pa.carrying_out_organization_id) org_name

,(SELECT DISTINCT organization_code

FROM org_organization_definitions

WHERE organization_id = pa.carrying_out_organization_id) org_code

,pa.project_id

,pa.org_id

,pa.NAME

,pa.long_name

,pa.segment1

,pa.project_status_code

,pab.burdened_cost budget_cost

,pa.created_by

,pa.project_type

,pa.start_date

,pa.completion_date

,pa.closed_date

,pa.carrying_out_organization_id

,NVL((SELECT DISTINCT ppf.first_name || ' ' || ppf.last_name

FROM pa_project_players p

,per_all_people_f ppf

WHERE ppf.person_id = p.person_id

AND project_role_type = 'PROJECT MANAGER'

AND p.project_id = pa.project_id), 'Not Defined') key_member

,pt.task_id

,pt.task_number

,pt.attribute1 task_status

,pt.task_name

,pt.description

,pab.budget_version_id

,trunc(pab.cur_base_date) approval_date

,(SELECT SUM(burdened_cost)

FROM pafv_budget_lines

WHERE task_id = pt.task_id

AND budget_version_id = pab.budget_version_id

GROUP BY task_id) task_budget

,pt.top_task_id

,pt.wbs_level

,pt.start_date

,pt.completion_date

,NULL destination_type_code

,mmt.inventory_item_id item

,(SELECT description

FROM mtl_system_items_kfv k

WHERE inventory_item_id = mmt.inventory_item_id

AND organization_id = pa.carrying_out_organization_id) item_desc

,(SELECT concatenated_segments

FROM mtl_system_items_kfv k

WHERE inventory_item_id = mmt.inventory_item_id

AND organization_id = pa.carrying_out_organization_id) item_code

,NULL po_num

,NULL vendor_name

,NULL Currency_Code

,NULL po_date

,NULL po_qty

,NULL po_unit_price

,NULL po_commit_cost

,NULL rec_num

,NULL rec_date

,NULL rec_qty

,NULL rec_price

,NULL rec_val

,CASE

WHEN mmt.transaction_quantity > 0 THEN

decode(mmt.transaction_type_id, 66,

(SELECT DISTINCT segment1

FROM pa_projects_all

WHERE project_id = mmt.to_project_id), 67,

(SELECT DISTINCT segment1

FROM pa_projects_all

WHERE project_id = mmt.to_project_id), 68,

(SELECT DISTINCT segment1

FROM pa_projects_all

WHERE project_id = mmt.to_project_id))

END AS transfered_project_from_other

,CASE

WHEN mmt.transaction_quantity > 0 THEN

DECODE(mmt.transaction_type_id, 66, mmt.transaction_quantity, 67,

mmt.transaction_quantity, 68, mmt.transaction_quantity)

END AS transfered_QTY_from_other

,CASE

WHEN mmt.transaction_quantity > 0 THEN

DECODE(mmt.transaction_type_id, 66, TRUNC(mmt.transaction_date), 67,

TRUNC(mmt.transaction_date), 68, TRUNC(mmt.transaction_date))

END AS transfered_DATE_from_other

,CASE

WHEN mmt.transaction_quantity > 0 THEN

DECODE(mmt.transaction_type_id, 66,

(mmt.transaction_quantity * mmt.actual_cost), 67,

(mmt.transaction_quantity * mmt.actual_cost), 68,

(mmt.transaction_quantity * mmt.actual_cost))

END AS transfered_cost_from_other

,CASE

WHEN mmt.transaction_quantity < 0 THEN

decode(mmt.transaction_type_id, 66,

(SELECT DISTINCT segment1

FROM pa_projects_all

WHERE project_id = mmt.to_project_id), 67,

(SELECT DISTINCT segment1

FROM pa_projects_all

WHERE project_id = mmt.to_project_id), 68,

(SELECT DISTINCT segment1

FROM pa_projects_all

WHERE project_id = mmt.to_project_id))

END AS transfer_project_to_other

,CASE

WHEN mmt.transaction_quantity < 0 THEN

DECODE(mmt.transaction_type_id, 66, mmt.transaction_quantity, 67,

mmt.transaction_quantity, 68, mmt.transaction_quantity)

END AS transfer_qty_to_other

,CASE

WHEN mmt.transaction_quantity < 0 THEN

DECODE(mmt.transaction_type_id, 66,

(mmt.transaction_quantity * mmt.actual_cost), 67,

(mmt.transaction_quantity * mmt.actual_cost), 68,

(mmt.transaction_quantity * mmt.actual_cost))

END AS transfer_cost_to_other

,CASE

WHEN mmt.transaction_quantity < 0 THEN

DECODE(mmt.transaction_type_id, 66, TRUNC(mmt.transaction_date), 67,

TRUNC(mmt.transaction_date), 68, TRUNC(mmt.transaction_date))

END AS transfer_DATE_to_other

,DECODE(mmt.transaction_type_id, 120, mmt.transaction_quantity, 35,

mmt.transaction_quantity, 63, mmt.transaction_quantity) issued_qty

,DECODE(mmt.transaction_type_id, 120, TRUNC(mmt.transaction_date), 35,

TRUNC(mmt.transaction_date), 63, TRUNC(mmt.transaction_date)) issued_date

,DECODE(mmt.transaction_type_id, 120,

(mmt.transaction_quantity * mmt.actual_cost * (-1)), 35,

(mmt.transaction_quantity * mmt.actual_cost * (-1)), 63,

(mmt.transaction_quantity * mmt.actual_cost * (-1))) issued_cost

,DECODE(mmt.transaction_type_id, 120,

(SELECT DISTINCT request_number

FROM mtl_txn_request_headers

WHERE header_id = mmt.transaction_source_id), 63,

(SELECT DISTINCT request_number

FROM mtl_txn_request_headers

WHERE header_id = mmt.transaction_source_id), 35,

(SELECT DISTINCT wip_entity_name

FROM wip_entities

WHERE wip_entity_id = mmt.transaction_source_id)) Issued_Ref_No

,NULL expense_loaded_to_project

,mmt.to_project_id

,mmt.transaction_quantity

,(SELECT description

FROM mtl_transaction_types

WHERE transaction_type_id = mmt.transaction_type_id

AND mmt.transaction_action_id = transaction_action_id

AND transaction_source_type_id = mmt.transaction_source_type_id) transaction_type

,mmt.subinventory_code

,mmt.locator_id

,(SELECT segment1 || '.' || segment2 || '.' || segment3

FROM mtl_item_locations

WHERE inventory_location_id = mmt.locator_id

AND organization_id = pa.carrying_out_organization_id) LOCATION

,mmt.transaction_uom

,mmt.transaction_date trans_date

,ROUND(NVL(mmt.transaction_cost, mmt.actual_cost), 2) per_unit_COST

FROM mtl_material_transactions mmt

,pa_projects_all pa

,pa_tasks pt

,po_lines_all pll

,pa_budget_versions_draft_v pab

,mtl_txn_request_lines mtxn

,pa_budget_types ty

WHERE mmt.transaction_set_id = mtxn.transaction_header_id(+)

AND pa.project_id = mmt.project_id(+)

AND pt.project_id = pa.project_id

AND pa.project_id = pab.project_id

AND ty.budget_type_code = pab.budget_type_code

AND pt.task_id = mmt.task_id

AND mmt.transaction_source_id = pll.po_header_id(+)

AND ty.plan_type = 'BUDGET'

AND mmt.inventory_item_id = pll.item_id(+)

AND mmt.transaction_type_id NOT IN (18, 36)

AND pa.org_id = :p_org_id

AND pa.segment1 = :p_from_segment

/* AND pa.project_status_code = NVL (:p_status, pa.project_status_code)

AND pa.segment1 BETWEEN NVL (:p_from_segment, pa.segment1)

AND NVL (:p_to_segment, pa.segment1)

AND TRUNC (pa.start_date) BETWEEN NVL

(TRUNC (TO_DATE (:p_from_start_date,

'DD-MON-RRRR'

)

),

TRUNC (pa.start_date)

)

AND NVL (TRUNC (TO_DATE (:p_to_start_date,

'DD-MON-RRRR'

)

),

TRUNC (pa.start_date)

)

AND pab.burdened_cost > = NVL (:p_cost, pab.burdened_cost)*/

ORDER BY item

,trans_date

Leia mais

[K2 PARTNERING] – 1 VAGA – Arquiteto Oracle Sr – 6 MESES

Pessoal, boa tarde! Me chamo Leonardo lira e estou com a seguinte oportunidade para início imediato: Desenvolvedor Oracle Public Revenue Sr Local do projeto: BRASÍLIA (DESPESAS PAGAS: Flybacks…

Leia mais

[K2 PARTNERING] – 1 VAGA – Oracle Desenvolvedor Oracle Public Revenue Sr – 6 MESES – INGLÊS FLUENTE

Pessoal, boa tarde! Me chamo Leonardo lira e estou com a seguinte oportunidade para início imediato: Desenvolvedor Oracle Public Revenue Sr Local do projeto:…

Leia mais

Oportunidade TCS (CLT): Analista de Sistemas Pleno e Senior

Olá All, bom dia! Segue abaixo oportunidade! Obs CVs fora do perfil serão desconsiderados! Grupo TATA – TCS Tata Consultancy Service 2 Vagas (CLT)…

Leia mais

Pleno RI

Contratação! Oportunidade de emprego para Pleno com alguma experiência em RI. Interessados enviam CV e pretensão para aricciardi@itconvergence.com Agustina RicciardiRecruitment ManagerIT CONVERGENCE JOIN us…

Leia mais

ORACLE CLOUD CONTRACT OPPORTUNITIES

I am looking for Oracle Cloud Consultants for an upcoming Oracle Cloud Project based primarily in Mexico City (other locations may be available). Specific details…

Leia mais

Oportunidade EBS Financials Chile

Bom dia grupo! Estou com uma oportunidade para consultor EBS Finanças (AR/AP/GL/Tax) para rollout em Santiago-Chile, iniciando em duas semanas com duração prevista…

Leia mais

Oracle Functional Finance Consultant based near Porto in Portugal

Bom dia pessoal, Segue uma vaga.. Se alguem se interessar, mande diretamente para o recruiter: Paul Still <PaulS@appsit.com> (Favor de não mandar para mim). Obrigado, Sam…

Leia mais

VAGAS de Oracle TECH e HCM | Dev Java | Arquiteto de integração

Pessoal, boa tarde! Seguem as oportunidades….. Interessados enviar curriculum com nome da vaga + pretensão salarial (PJ ou CLT) + disponibilidade para…

Leia mais

Vaga GSN Consultor Tecnico Mastersaf

Consultor Tecnico Mastersaf São Paulo-SP Experiência: Instalação/Atualização Mastersaf DW e Onesource; *Necessário ter notebook *Período: Indeterminado *Disponibilidade para Viagens CV para gabriela.vaccari@gsn.com.br assunto tecnico…

Leia mais

Vagas Oracle Cloud OM, GOP, INV/QA, Manutenção e Unifer Configuration – para SP e RJ

Olá Pessoal, Seguem as oportunidades….. =========================================================================== *** Interessados enviar curriculum com o número da vaga + pretensão salarial + forma contratual ( CLT…

Leia mais

OPORTUNIDADE RIO DE JANEIRO

Consultor Oracle PTP / FINZ Ingles fluente Inicio imediato Longo prazo Local Rio de Janeiro PJ Interessados e indicacoes favor enviar email para aricciardi@itconvergence.com…

Leia mais

Vaga rtr

Oi Gabriela, Acho que voce quer enviar email para o Edson Carneiro e eu sou Edson Ziemann. Grato Edson Em sáb, 29 de jun…

Leia mais

Vaga GSN – Consultor Oracle RTR

Consultor Oracle RTR Inicio Imediato Projeto de 1 mes Local: Berrini-SP Necessário Notebook Vaga PJ CV para gariela.vaccari@gsn.com.br assunto RTR com pretensão PJ

Leia mais

Oportunidade Consultor EBS RI

Pessoal, bom dia! Estamos com oportunidade para Consultor Funcional EBS Suprimentos (RI principalmente) com inglês fluente, desejável conhecimento no EFD Loader da Oracle. Projeto…

Leia mais

Vaga GSN- DBA ORACLE

ANALISTA SR DBA – Oracle/Exadata (Administração/Suporte) Pinheiros-SP Vaga PJ Conhecimentos Avançados: Oracle Enterprise Manager 10g/11g/12c Exadata GoldenGate DataGuard Data Masking Segurança RAC Performance Tunning…

Leia mais

Vaga Projeto em El Salvador

Olá amigos , Estamos com uma vaga em aberto para um projeto em El Salvador , alocação de 4 a 6 meses . Perfil…

Leia mais

Consultor Funcional OTC

Bom dia, Oportunidade BERTINI DO BRASIL Atenciosamente, Livre de vírus. www.avg.com.

Leia mais

VAGA DBA ORACLE – AV. PAULISTA

VAGA DBA ORACLE – AV. PAULISTA Experiência em Oracle RAC e projetos com Oracle Retail Região: Avenida Paulista – Início Imediato Interessados enviar…

Leia mais

ORACLE R12 EXPORTAR E IMPORTAR ATRIBUTOS DO QUALITY QA_RESULTS

BEGIN
  
CREATE TABLE XONT_QA_RESULTS AS SELECT * FROM QA_RESULTS;

CURSOR get_details IS
SELECT ROWID
      ,column1
      ,column2
      ,column3
      ,column4
      ,column5
      ,column6
      ,column7
      ,column8
      ,column9
      ,column10
      ,column11
      ,column12
  FROM XONT_QA_RESULTS
 WHERE NVL(process_flag, 'N') = 'N'
 ORDER BY page_seq
         ,main_seq
         ,item_in_grp_Seq;

l_user_id NUMBER := FND_PROFILE.VALUE('USER_ID');
l_plan_id QA_PLANS_V.plan_id%TYPE;
ln_request_id NUMBER;
l_collection_id NUMBER;
l_return BOOLEAN;
l_phase VARCHAR2(100);
l_status VARCHAR2(100);
l_return_status VARCHAR2(100);
l_dev_phase VARCHAR2(100);
l_dev_status VARCHAR2(100);
l_message VARCHAR2(100);

BEGIN
SELECT QA_COLLECTION_ID_S.NEXTVAL
INTO l_collection_id
FROM DUAL;

SELECT plan_id
INTO l_plan_id
FROM QA_PLANS_V
WHERE name = 'PLAN NAME1';

BEGIN
UPDATE XONT_QA_RESULTS
SET process_flag = 'D' -- Unprocessed Status where Attachment has not been uploaded
WHERE process_flag = 'P';
EXCEPTION
WHEN NO_DATA_FOUND THEN
FND_FILE.PUT_LINE(FND_FILE.LOG, 'No existing "Processed" Data to be updated as D')
END;

FOR get_dtls IN get_details

LOOP
INSERT INTO qa_results_interface
transaction_interface_id
,organization_code
,plan_name
,process_status
,insert_type
,plan_id
,collection_id
,last_update_date
,last_updated_by
,qa_last_updated_by
,transaction_date
,character1
,character2
,character3
,character4
,character5
,character6
,character7
,character8
,character9
,character10
,character11
,character12
)
VALUES (
QA_RESULTS_UPDATE_HISTORY_S.nextval,
'OR1'
,'PLAN NAME1'
,1 -- process_status
,1 -- For Update
,l_plan_id--
,l_collection_id
,SYSDATE
,l_user_id
,l_user_id
,SYSDATE
, get_dtls.column1
, get_dtls.column2
, get_dtls.column3
, get_dtls.column4
,get_dtls.column5
,get_dtls.column6
,get_dtls.column7
,get_dtls.column8
,get_dtls.column9
,get_dtls.column10
,get_dtls.column11
,get_dtls.column12
);

UPDATE Xont_qa_results
SET process_flag = 'P'
WHERE column12 = get_dtls.COLUMN12
AND process_Flag = 'N';
END LOOP;

COMMIT;

ln_request_id :=
fnd_request.submit_request ('QA', -- application
'QLTTRAMB', -- program short name
'', -- description
'', -- start time
FALSE, -- sub request
'200',
'1',
l_user_id,
2, CHR (0));
COMMIT;

IF ln_request_id = 0
THEN
fnd_file.put_line(fnd_file.log,'failed in concurrent request submission');

ELSE
fnd_file.put_line(fnd_file.log, 'Submitted request for Import.');

END IF;
fnd_file.put_line(fnd_file.log, 'Completed process');

END;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

BEGIN

CREATE TABLE XONT_QA_RESULTS AS SELECT * FROM QA_RESULTS;

CURSOR get_details IS

SELECT ROWID

,column1

,column2

,column3

,column4

,column5

,column6

,column7

,column8

,column9

,column10

,column11

,column12

FROM XONT_QA_RESULTS

WHERE NVL(process_flag, 'N') = 'N'

ORDER BY page_seq

,main_seq

,item_in_grp_Seq;

l_user_id NUMBER := FND_PROFILE.VALUE('USER_ID');

l_plan_id QA_PLANS_V.plan_id%TYPE;

ln_request_id NUMBER;

l_collection_id NUMBER;

l_return BOOLEAN;

l_phase VARCHAR2(100);

l_status VARCHAR2(100);

l_return_status VARCHAR2(100);

l_dev_phase VARCHAR2(100);

l_dev_status VARCHAR2(100);

l_message VARCHAR2(100);

BEGIN

SELECT QA_COLLECTION_ID_S.NEXTVAL

INTO l_collection_id

FROM DUAL;

SELECT plan_id

INTO l_plan_id

FROM QA_PLANS_V

WHERE name = 'PLAN NAME1';

BEGIN

UPDATE XONT_QA_RESULTS

SET process_flag = 'D' -- Unprocessed Status where Attachment has not been uploaded

WHERE process_flag = 'P';

EXCEPTION

WHEN NO_DATA_FOUND THEN

FND_FILE.PUT_LINE(FND_FILE.LOG, 'No existing "Processed" Data to be updated as D')

END;

FOR get_dtls IN get_details

LOOP

INSERT INTO qa_results_interface

transaction_interface_id

,organization_code

,plan_name

,process_status

,insert_type

,plan_id

,collection_id

,last_update_date

,last_updated_by

,qa_last_updated_by

,transaction_date

,character1

,character2

,character3

,character4

,character5

,character6

,character7

,character8

,character9

,character10

,character11

,character12

)

VALUES (

QA_RESULTS_UPDATE_HISTORY_S.nextval,

'OR1'

,'PLAN NAME1'

,1 -- process_status

,1 -- For Update

,l_plan_id--

,l_collection_id

,SYSDATE

,l_user_id

,SYSDATE

, get_dtls.column1

, get_dtls.column2

, get_dtls.column3

, get_dtls.column4

,get_dtls.column5

,get_dtls.column6

,get_dtls.column7

,get_dtls.column8

,get_dtls.column9

,get_dtls.column10

,get_dtls.column11

,get_dtls.column12

);

UPDATE Xont_qa_results

SET process_flag = 'P'

WHERE column12 = get_dtls.COLUMN12

AND process_Flag = 'N';

END LOOP;

COMMIT;

ln_request_id :=

fnd_request.submit_request ('QA', -- application

'QLTTRAMB', -- program short name

'', -- description

'', -- start time

FALSE, -- sub request

'200',

'1',

l_user_id,

2, CHR (0));

COMMIT;

IF ln_request_id = 0

THEN

fnd_file.put_line(fnd_file.log,'failed in concurrent request submission');

ELSE

fnd_file.put_line(fnd_file.log, 'Submitted request for Import.');

END IF;

fnd_file.put_line(fnd_file.log, 'Completed process');

END;

Leia mais